IDENTITY INSERT is set to OFF - How to turn it ON

Question

I have a deleted file archive database that stores the ID of the file that was deleted   I want the admin to be able to restore the file  as well as the same ID for linking files    I do not want to take identity insert off the entire table  as the increment by one works great   In my insert to TBL Content store procedure I have something like this  set ANSI NULLS ON set QUOTED IDENTIFIER ON SET IDENTITY INSERT tbl content ON GO  ALTER procedure  dbo   spInsertDeletedIntoTBLContent   ContentID int      insert command    SET IDENTITY INSERT tbl content OFF   But I keep getting the same error      Cannot insert explicit value for identity column in table    TBL Content  when IDENTITY INSERT is set to OFF    Any help

User · Answer

The Reference   http   technet microsoft com en-us library aa259221 28v sql 80 29 aspx  My table is named Genre with the 3 columns of Id  Name and SortOrder  The code that I used is as   SET IDENTITY INSERT Genre ON  INSERT INTO Genre Id  Name  SortOrder VALUES  12  Moody Blues   20

User · Answer

I believe it needs to be done in a single query batch   Basically  the GO statements are breaking your commands into multiple batches and that is causing the issue   Change it to this   SET IDENTITY INSERT tbl content ON    GO        insert command     SET IDENTITY INSERT tbl content OFF GO

User · Answer

Add set off also   SET IDENTITY INSERT Genre ON      INSERT INTO Genre Id  Name  SortOrder VALUES  12  Moody Blues   20        SET IDENTITY INSERT Genre  OFF

User · Answer

Shouldn t you be setting identity Insert ON  inserting the records and then turning it back off   Like this   set ANSI NULLS ON set QUOTED IDENTIFIER ON SET IDENTITY INSERT tbl content ON GO  ALTER procedure  dbo   spInsertDeletedIntoTBLContent   ContentID int   SET IDENTITY INSERT tbl content ON    insert command    SET IDENTITY INSERT tbl content OFF

User · Answer

Add this line above you Query  SET IDENTITY INSERT tbl content ON

User · Answer

Reminder  SQL Server only allows one table to have IDENTITY INSERT property set to ON   This does not work   SET IDENTITY INSERT TableA ON SET IDENTITY INSERT TableB ON     INSERT ON TableA         INSERT ON TableB     SET IDENTITY INSERT TableA OFF SET IDENTITY INSERT TableB OFF   Instead   SET IDENTITY INSERT TableA ON     INSERT ON TableA     SET IDENTITY INSERT TableA OFF SET IDENTITY INSERT TableB ON     INSERT ON TableB     SET IDENTITY INSERT TableB OFF

User · Answer

Should you instead be setting the identity insert to on within the stored procedure   It looks like you re setting it to on only when changing the stored procedure  not when actually calling it   Try   ALTER procedure  dbo   spInsertDeletedIntoTBLContent   ContentID int    SET IDENTITY INSERT tbl content ON     insert command     SET IDENTITY INSERT tbl content OFF GO

[sql] IDENTITY_INSERT is set to OFF - How to turn it ON?

Examples related to sql

Examples related to sql-server

Examples related to tsql