I was editing some script in SQL Server Management Studio.

And then suddenly this error appeared:

IDENTITY_INSERT is already ON for table [Table_Name]

So, how did I come to this error?

My script looked like one below:

SET IDENTITY_INSERT [TABLE_NAME] ON

–insert statements

failure on one of inserts because of not existing column

SET IDENTITY_INSERT [TABLE_NAME] ON

 

The script failed on one of the inserts, because of a missing column.

I added a column to fix the problem but then I started getting this error: IDENTITY_INSERT is already ON for table [Table_Name].

I tried to run inserts independently but error persisted.

Why does it happen?

IDENTITY_INSERT works in scope of one session. If you read corresponding MSDN page it says: “At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.”

How to solve?

You just need another session. I simply reconnected to the same database and it run without any issues.

Of course I changed the script to set IDENTITY_INSERT to OFF in the end.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInEmail this to someone