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?

Answer 1

It looks like you are trying to set it, only when changing the stored procedure, not when actually calling it.

Try this code:

ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]

@ContentID int, 

SET IDENTITY_INSERT tbl_content ON

...insert command...

SET IDENTITY_INSERT tbl_content OFF

GO