复制时插入带有标识列的表会导致 SQL Server 出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36051593/
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
Inserting into table with an Identity column while replication causes error in SQL Server
提问by agm92
I have a table A_tblin my database. I have created a trigger on A_tblto capture inserted records. Trigger is inserting records in my queue table B_tbl. This table has an Identitycolumn with property "Not for replication" as 1.
A_tbl我的数据库中有一张表。我创建了一个触发器A_tbl来捕获插入的记录。触发器正在我的队列表中插入记录B_tbl。该表有一Identity列的属性“不用于复制”为 1。
A_tbl(Id, name, value) withIdas the primary keyB_tbl(uniqueId, Id) withuniqueIdasIdentitycolumn
A_tbl(Id, name, value) withId作为主键B_tbl(uniqueId, Id) withuniqueIdasIdentity列
Trigger code doing this:
执行此操作的触发代码：
Insert into B_tbl (Id)
select i.Id from inserted
Now my table 'B' is replicated to another DB Server, now when I'm inserting into table 'A' it is causing this error:
现在我的表 'B' 被复制到另一个数据库服务器，现在当我插入表 'A' 时，它导致了这个错误：
Explicit value must be specified for identity column in table 'B_tbl' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. (Source: MSSQLServer, Error number: 545)
当 IDENTITY_INSERT 设置为 ON 或复制用户插入 NOT FOR REPLICATION 标识列时，必须为表 'B_tbl' 中的标识列指定显式值。（来源：MSSQLServer，错误号：545）
Please help me resolve this issue.
请帮我解决这个问题。
回答by bmsqldev
You have to do something like this
你必须做这样的事情
SET IDENTITY_INSERT A_tbl ON
Insert into B_tbl (uniqueid, Id)
select 1, i.id from inserted
SET IDENTITY_INSERT A_tbl OFF
回答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
回答by Adarsh H D Dev
The trigger code should contain the Identity insert ON option as below
触发代码应包含身份插入 ON 选项，如下所示
SET IDENTITY_INSERT B_tbl ON
Insert into B_tbl (uniqueid,Id)
select identityvalue,i.Id from inserted
SET IDENTITY_INSERT B_tbl OFF
