Cannot insert explicit value because IDENTITY_INSERT is OFF, but cannot SET IDENTITY_INSERT to ON because it is already ON

Question

I have a table in database Foo named Bar, that has a column named ID, which is the primary key, and this database is living on the development SQL Server.

I'm trying to copy data from our production server into the development server so I can play with said data, so I execute the following:

set IDENTITY_INSERT Foo.dbo.Bar ON
insert into Foo.dbo.Bar
(
   ID
   ,Something
   ,Else
   ,Is
   ,Going
   ,Horribly
   ,Wrong
   ,With
   ,SQL
)
select 
  ID
  ,Something
  ,Else
  ,Is
  ,Going
  ,Horribly
  ,Wrong
  ,With
  ,SQL
from Production.Foo.dbo.Bar

set IDENTITY_INSERT Foo.dbo.Bar OFF

And I get the error

Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'Foo.dbo.Bar'. Cannot perform SET operation for table 'Foo.dbo.Bar'.

Hmm..okay, so IDENTITY_INSERT is turned on for the table. So I removed the SET IDENTITY_INSERT Foo.dbo.Bar ON from the top of my query, and then execute it, and I get this error:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Bar' when IDENTITY_INSERT is set to OFF.

I can execute SET IDENTITY_INSERT Foo.dbo.Bar OFF all day long, but if I try to turn it ON, then SQL Server 2012 says that IDENTITY_INSERT is already turned on.

Answer 1

Funny - i just had the exact same problem with 3 of 4 tables in my brand new ss2012 database.

So i rewrote the table create script to bracket my inserts with 'set identity on' and 'set identity off' and it works. It seems like ss only wants to handle one table's identity at a time ie you cant set a new table identity on until all the others have been turned off.

something like this:

create table Employers
(
id int PRIMARY KEY IDENTITY ,
companyid nvarchar(50) not null,
companyName nvarchar(80) not null,
address nvarchar(80),
Phone nvarchar(10),
);

SET IDENTITY_INSERT employers ON;

insert into Employers(id,companyid,companyName,address,Phone)
Values
(...),
(...)

SET IDENTITY_INSERT employers OFF;

create table customers
(
...