SQL Server 2008 插入时出现“IDENTITY_INSERT 已打开”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23100189/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 "IDENTITY_INSERT is ON" error on Insert
提问by RanPaul
I have Locale_Codetable in two different databases CPand PP
我有Locale_Code表在两个不同的数据库CP和PP
here is the insert script of my table
这是我的表的插入脚本
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
Am trying to insert all rows from PP.dbo.LOCALE_CODE to CP.dbo.LOCALE_CODE as below
我试图将所有行从 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
But am getting below error
但我得到低于错误
An explicit value for the identity column in table 'CP.dbo.LOCALE_CODE' can only be specified when a column list is used and IDENTITY_INSERT is ON.
表 'CP.dbo.LOCALE_CODE' 中标识列的显式值只能在使用列列表且 IDENTITY_INSERT 为 ON 时指定。
I even tried bulk insert as below
我什至尝试过如下批量插入
BULK INSERT CP.[dbo].[LOCALE_CODE] from 'C:\locales.csv'
with (fieldterminator = ',', rowterminator = '\n' )
go
But am getting below error
但我得到低于错误
Explicit value must be specified for identity column in table 'LOCALE_CODE' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
当 IDENTITY_INSERT 设置为 ON 或复制用户插入 NOT FOR REPLICATION 标识列时，必须为表 'LOCALE_CODE' 中的标识列指定显式值。
Can someone please help to fix wither BULK insert or direct insert from PP.Locale_Code to PP.Locale_Code?
有人可以帮忙修复散装插入或从 PP.Locale_Code 直接插入到 PP.Locale_Code 吗？
回答by Mudassir Hasan
You need to explicitly mention column names in target table while inserting .
Replace Col1,Col2 etc with column list of CP.[dbo].[LOCALE_CODE]table
您需要在插入时明确提及目标表中的列名。用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
You can use * in SELECT of source table PP.dbo.LOCALE_CODE
您可以在源表的 SELECT 中使用 * PP.dbo.LOCALE_CODE
回答by QA Specialist
There are basically 2 different ways to INSERT records without having an error:
基本上有两种不同的方法可以插入记录而不会出错：
1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENT
1) 当 IDENTITY_INSERT 设置为 OFF 时。主键“ID”不得存在
2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY "ID" MUST BE PRESENT
2) 当 IDENTITY_INSERT 设置为 ON 时。必须存在主键“ID”
As per the following example from the same Table created with an IDENTITY PRIMARY KEY:
根据使用 IDENTITY PRIMARY KEY 创建的同一个表中的以下示例：
CREATE TABLE [dbo].[Persons] (
ID INT IDENTITY(1,1) PRIMARY KEY,
LastName VARCHAR(40) NOT NULL,
FirstName VARCHAR(40)
);
1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY "ID" MUST NOT BE PRESENTfrom the "INSERT INTO" Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error "Cannot insert explicit value for identify column in table..."
1) 在第一个示例中，当 IDENTITY_INSERT 为 OFF 时，您可以将新记录插入表中而不会出错。PRIMARY KEY “ID”不得出现在“INSERT INTO”语句中，并且会自动添加唯一的 ID 值：。如果在这种情况下 ID 来自 INSERT，您将收到错误“无法为表中的标识列插入显式值...”
SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE');
INSERT INTO Persons (FirstName,LastName)
VALUES ('JOE','BROWN');
OUTPUT of TABLE [dbo].[Persons] will be:
表 [dbo].[Persons] 的输出将是：
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY "ID" MUST BE PRESENTfrom the "INSERT INTO" Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error "Explicit value must be specified for identity column table..."
2) 在第二个示例中，当 IDENTITY_INSERT 为 ON 时，您可以将新记录插入表中而不会出错。只要 ID 值不存在，就必须从“INSERT INTO”语句中存在主键“ID”：如果在这种情况下从 INSERT 中不存在 ID，您将收到错误“显式值必须是为标识列表指定...”
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,'Hyman','BLACK');
OUTPUT of TABLE [dbo].[Persons] will be:
表 [dbo].[Persons] 的输出将是：
ID LastName FirstName
1 DOE Jane
2 BROWN JOE
3 BLACK Hyman
5 WHITE JOHN
