T-SQL Error

IDENTITY_INSERT is already ON for table schema.SomeTableName. Cannot perform SET operation for table Table Name.

Solution

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.  So when you try to set it ON for another table, without turning if off for the first table, this error is generated.

Enter this command for the first table mentioned in the above error.

SET IDENTITY_INSERT [yourSchema].[yourTable] OFF

Context

This happened to me when moving data from one SQL Server to another using RedGate synchronization script.  The script was too large to run all at once; so I had to cut it into smaller pieces.  That utility turns off IDENTITY_INSERT before each set of inserts for each table, then turns it back on at the end of those inserts.

 

 

Filed under: SQL