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

Question

I'm trying to rebuild a database from a text file, using VBA. To insert the ID values, I call this before sending in the INSERTs:

SET IDENTITY_INSERT TableName ON

This seems to work fine for most TableNames, but when I call it on table 'Accounts' I get an interesting error:

"IDENTITY_INSERT is already ON for table 'test.dbo.PropertyAssets'. Cannot perform SET operation for table 'Accounts'."

I'm guessing this has to do with fkey constraints or such, but does anyone know for sure?

Answer 1

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