“IDENTITY_INSERT is already ON for table X” When Applied to Multiple Tables

Today, I needed to insert data into multiple tables in SQL Server and write to the tables’ identity columns. My SQL script looked like this:

SET IDENTITY_INSERT Table1 ON
SET IDENTITY_INSERT Table2 ON
SET IDENTITY_INSERT Table3 ON

-- insert statements go here

SET IDENTITY_INSERT Table1 OFF
SET IDENTITY_INSERT Table2 OFF
SET IDENTITY_INSERT Table3 OFF

The first time I ran the script, I got the following error at line 2: IDENTITY_INSERT is already ON for table ‘Table1’. Cannot perform SET operation for table ‘Table2’. What’s wrong?Read More »