“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?

The answer is simple: “At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.” So the fix was easy:

SET IDENTITY_INSERT Table1 ON
-- insert statements for table1
SET IDENTITY_INSERT Table1 OFF

SET IDENTITY_INSERT Table2 ON
-- insert statements for table2
SET IDENTITY_INSERT Table2 OFF

SET IDENTITY_INSERT Table3 ON
-- insert statements for table3
SET IDENTITY_INSERT Table3 OFF