IDENTITY_INSERT is set to OFF - How to turn it ON?
SqlSql ServerTsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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
Solution 3 - Sql
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
Solution 4 - Sql
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
Solution 5 - Sql
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)
Solution 6 - Sql
Add this line above you Query
SET IDENTITY_INSERT tbl_content ON
Solution 7 - Sql
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
Solution 8 - Sql
I had two different tables and for the first one insert works fine but for the second one throws this exception regarding Identity Insert. The issue was that the table had a field(int) as a forgery key which wasn't populated.