SQL Server 2008在插入时出现“IDENTITY_INSERT is ON”错误

Question

我有一个Locale_Code表在两个不同的数据库CP和PP中。

这是我的表插入脚本：

CREATE TABLE [dbo].[LOCALE_CODE](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
[active] [bit] NOT NULL,
[code] [nvarchar](4000) NULL,
[created_at] [datetime] NULL,
[created_by] [nvarchar](4000) NULL,
[display_name] [nvarchar](4000) NULL,
[updated_at] [datetime] NULL,
[updated_by] [nvarchar](4000) NULL,
[read_permission] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

我正在尝试将PP.dbo.LOCALE_CODE中的所有行插入到CP.dbo.LOCALE_CODE中，如下所示：

SET IDENTITY_INSERT CP.dbo.LOCALE_CODE ON

insert into CP.[dbo].[LOCALE_CODE] select * from PP.dbo.LOCALE_CODE  

但是我遇到了以下错误：

在使用列清单并且启用了 IDENTITY_INSERT 时，只能为表 'CP.dbo.LOCALE_CODE' 中的标识列指定显式值。

我甚至尝试了以下批量插入操作：

BULK INSERT CP.[dbo].[LOCALE_CODE] from 'C:\locales.csv'
with (fieldterminator = ',', rowterminator = '\n' )
go

但是我遇到了以下错误

在表 'LOCALE_CODE' 中，当 IDENTITY_INSERT 设置为 ON 时或者复制用户插入到非用于复制的标识列时，必须为标识列指定显式值。

请问有人能帮助修复BULK插入或直接从PP.Locale_Code插入到PP.Locale_Code吗？

- OTUser

---

请更加注意错误信息所说的内容。"仅当使用列列表并且启用了IDENTITY_INSERT时，才能为表 'CP.dbo.LOCALE_CODE' 中的标识列指定显式值。"你没有使用列列表。 - user743382

Answer 1

在插入时，您需要明确提及目标表中的列名。 请使用CP.[dbo].[LOCALE_CODE]表的列清单替换Col1、Col2等列名。

SET IDENTITY_INSERT CP.dbo.LOCALE_CODE ON

insert into CP.[dbo].[LOCALE_CODE] (Col1 ,Col2 , Col3) 
select * from PP.dbo.LOCALE_CODE 

您可以在源表PP.dbo.LOCALE_CODE的SELECT语句中使用星号(*)。

- Mudassir Hasan

Answer 2

在不出现错误的情况下，基本上有两种不同的方法来插入记录：

1) 当IDENTITY_INSERT关闭时。主键"ID"不能存在。

2) 当IDENTITY_INSERT开启时。主键"ID"必须存在。

根据以下示例，使用具有IDENTITY PRIMARY KEY的相同表创建：

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1）在第一个示例中，当IDENTITY_INSERT关闭时，您可以将新记录插入表中而不会出现错误。 从“INSERT INTO”语句中必须删除 PRIMARY KEY "ID"的存在，系统将自动添加唯一ID值：如果在此情况下INSERT中存在ID，则会出现错误“无法在表中为标识列插入显式值...”

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

[dbo].[Persons]表的输出结果如下：

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) 在第二个例子中，当IDENTITY_INSERT打开时，您可以向表中插入新记录而不会出现错误。主键"ID"必须存在于"INSERT INTO"语句中只要ID值不存在：如果在此情况下从INSERT中不提供ID，则会出现错误"Explicit value must be specified for identity column table..."。

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK'); 

[dbo].[Persons]表的输出结果如下：

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

- Supercoder