Confusing IDENTITY_INSERT error, says it is already on in another table

You can only set IDENTITY_INSERT on for one table at a time in your session. If you want to use it on multiple tables, you need to set it ON and OFF consistently, see the template below:

SET IDENTITY_INSERT TableName ON

--insert values with IDs

SET IDENTITY_INSERT TableName OFF 

SET IDENTITY_INSERT PropertyAssets ON

--insert value with IDs

SET IDENTITY_INSERT PropertyAssets OFF

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.

Tags:

Sql Server

Vba