I was editing some script in SQL Server Management Studio.
And then suddenly this error appeared:
My script looked like one below:
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.
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.”
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.