I was editing some script in SQL Server Management Studio.
And then suddenly this error appeared:
So, how did I come to this error?
My script looked like one below:
–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.
20 comments
| Markdown | Result |
|---|---|
| *text* | text |
| **text** | text |
| ***text*** | text |
| `code` | code |
| ~~~ more code ~~~~ |
more code |
| [Link](https://www.example.com) | Link |
| * Listitem |
|
| > Quote | Quote |
Thanks for this. I had a maddening problem with this of there being only one IDENTITY_INSERT statement. I could then run SET IDENTITY_INSERT table OFF followed by SET IDENTITY_INSERT table ON and it would still fail.
Closing and re-opening the connection worked nicely.
Ian
Genuinely very helpful tip, thanks
– Mike
Yep, worked for me also – thanks very much!
Thanks, it is really helpful tip.
Still helpful
and still helpful!!
And still helpful!!! :-)
And still helpful!
Also worth pointing out that you can only set IDENTITY_INSERT ON on one table at a time. Attempting to set IDENTITY_INSERT ON on a second table without first switching it off on the first table will result in an error.
Thanks for pointing this out. I was having problems with nested IDENTITY_INSERT ON
it is worked in the new session… Thanks…..
Worked perfectly, thank you!
Thank you. Much appreciated. Was having this issue inside of a loop that was using a SqlBulkCopy adapter. Was trying to continue on error but it was throwing this exception. Just added an exception handler to turn off IDENTITY_INSERT on the table so the next one would continue without issue.
Thanks! Wasted hours and could not figure out why. Glad I came upon this post.
Thanks for this, I was trying to set a load of tables to IDENTITY_INSERT ON, then update, then switch them all off. A no-goer for sure.
reconnect to sql is work. thank!
Hi All,
I’m actually very surprised that this post is such a hit. It is 3rd visited page on this blog at the moment. I’ve just fixed few grammar issues, but, basically, left the post as it was.
Great to know that I was of some help to you.
Work for me too. Thanks!
Thank you for this tip. It worked for me!
Almost 14 years later, still helpful