无法插入显式值，因为IDENTITY_INSERT已关闭，但无法将IDENTITY_INSERT设置为ON，因为它已经是ON状态。

Question

我在数据库Foo中有一张名为Bar的表，其中有一列名为ID，它是主键，这个数据库存放在开发SQL Server上。

我正在尝试将数据从生产服务器复制到开发服务器，以便可以使用该数据，因此我执行以下操作：

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

并且我收到了错误信息

Msg 8107，级别16，状态1，行1
对于表'Foo.dbo.Bar'，IDENTITY_INSERT已经处于打开状态。无法为表'Foo.dbo.Bar'执行SET操作。

嗯……好的，所以这个表已经打开了IDENTITY_INSERT。因此，我从我的查询顶部删除了SET IDENTITY_INSERT Foo.dbo.Bar ON, 然后执行它，结果出现以下错误：

Msg 544，级别16，状态1，行1
当IDENTITY_INSERT设置为关闭时，无法在表“Bar”中插入显式值标识列。

我可以一直执行SET IDENTITY_INSERT Foo.dbo.Bar OFF，但如果我尝试将其打开ON，则SQL Server 2012会显示IDENTITY_INSERT已经打开。

- CurtisHx

---

它是否可能是从不同的会话中打开的？ - Aaron Bertrand

我无法重现这个错误。如果一个表的 IDENTITY_INSERT 开启，我可以一直将其设置为开启状态而不会出错；同样，如果它关闭了，我也可以将其设置为关闭状态，唯一不能将其设置为开启的时间是在同一会话中存在另一个表。如你所见，在 这个 sqlfiddle 中，前三批多次关闭和打开都能正常工作，只有第四批失败了。 - GarethD

我也无法重现这个错误。可能有以下三种情况：1）这是一个不太可能的SQL Server错误；2）它与某些分布式事务上下文相关，而我现在无法测试（不太可能）；或者3）您可能已经误读了实际名称/情况。 - RBarryYoung

我在新的查询窗口中执行了我的查询，成功解决了我的问题。感谢@AaronBertrand让我找对了方向。 - CurtisHx

请仔细检查错误中的表名是否与消息中的表名匹配。这个问题总是让我困扰，因为只有一个表可以同时启用identity_insert。 - Tim Abell

Answer 1

我实际上找到了一个不同的解决方案。这与"Msg 8101" 不同，它是特定于表身份未设置为ON的。

要解决这个问题，您需要删除主数据库中具有相同名称的模式和表。

我有一个模板脚本，可以创建数据库和表，并将数据插入这些表中，类似于：

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

问题是在数据库创建期间，由于“设备激活”错误（没有权限在创建MDF文件的文件夹中创建），或者由于数据库文件大小而导致语句失败。脚本继续执行，但是在master数据库中创建了模式和表。意识到错误后，我停止了脚本并尝试使用修复过的创建方式重新运行它。

然后我收到了错误提示。

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.

我并不是SQL Server专家，但我认为它首先在master数据库中查找模式，因为该模式已被发现且当前会话中已经启用了IDENTITY，所以它产生了这个错误信息。

在错误地创建的master数据库中删除模式和表之后，我的脚本顺利运行了。

- Xenno

---

不知何故，我的脚本在主数据库中创建了表，而不是在我的数据库中。正如你指出的那样删除它们就可以了。谢谢！你会注意到问题是：“插入到Foo.dbo...” 在我的情况下它说：“插入到master.dbo...”。 - pashute

Answer 2

有趣 - 我的全新ss2012数据库中有4张表，其中3张表出现了完全相同的问题。我重新编写了表格创建脚本，并使用“set identity on”和“set identity off”括起来插入数据，它奏效了。似乎ss只希望一次处理一个表的身份识别，也就是说，你不能在所有其他表关闭身份识别之前设置新表的身份识别。

类似这样的代码：

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

- jmaschle

Answer 3

我遇到了完全相同的错误。我正在使用 linq-to-sql，最初生成的表和 dbml 文件没有在主键上设置标识列。
后来我将主键更新为标识列，但忘记更新 dbml 文件。

当 IDENTITY_INSERT 设置为 OFF 时，在表 'BLAH' 中插入显式值的标识列无法插入

解决方法是更新 dbml 文件，有趣的是...

- JumpingJezza