SQL Server SET IDENTITY_INSERT

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

 

I was getting the message:

Msg 544, Level 16, State 1, Line 73
Cannot insert explicit value for identity column in table ‘NewInventory’ when IDENTITY_INSERT is set to OFF.

This was caused by a script trying to set multiple tables to “IDENTITY_INSERT ON”.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.