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
(
...

Answer 2

I actually found a different solution to this. This is actually different from Msg 8101, specific to table identity being not set to ON.

To resolve the problem, you need to drop the schema and tables of the same names in the master database.

I have a template script that creates a database and tables and inserts data into those tables, something like:

USE master
GO
CREATE DATABASE [mydb] ON  PRIMARY 
( NAME = N'mydb', FILENAME = N'C:\SQLDATA\mydb.mdf' , SIZE = 5072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'mydb_log', FILENAME = N'C:\SQLDATA\mydb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [mydb]
GO

CREATE SCHEMA [myschema] AUTHORIZATION [dbo]
GO

CREATE TABLE [myschema].[mytable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SOMETHING] [int] NOT NULL,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
GO

SET IDENTITY_INSERT [myschema].[mytable] ON 
GO

INSERT [myschema].[mytable] ([ID], [SOMETHING]) VALUES (1,2)
GO

SET IDENTITY_INSERT [myschema].[mytable] OFF
GO

The problem was that during the database creation, the statement failed due to the "device activation" error (permissions to the folder where the MDF file will be created), or because of the database file size. The script continued but created the schemas and tables in the master database instead. After I realised the error, I stopped the script and attempted to run it again with the create issues fixed.

Then I got the error.

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'master.myschema.mytable'. Cannot perform SET operation for table 'myschema.mytable'.
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'mytable' when IDENTITY_INSERT is   set to OFF.

I'm no SQL Server expert, but I think it looks up the schema in the master database first, and because this schema is found and in the current session IDENTITY is already on, it produces this error message.

After dropping the schemas and tables incorrectly created in the master database, my script ran successfully.