Inserting into table with an Identity column while replication causes error in SQL Server

Question

I have a table A_tbl in my database. I have created a trigger on A_tbl to capture inserted records. Trigger is inserting records in my queue table B_tbl. This table has an Identity column with property "Not for replication" as 1.

• A_tbl (Id, name, value) with Id as the primary key
• B_tbl (uniqueId, Id) with uniqueId as Identity column

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:

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)

Please help me resolve this issue.

Solution 1

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

Solution 2

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

2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY "ID" MUST BE PRESENT

As per the following example from the same Table created with an 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 PRESENT from 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..."

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:

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 PRESENT from 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..."

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'); 

OUTPUT of TABLE [dbo].[Persons] will be:

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

Solution 3

The trigger code should contain the Identity insert ON option as below

SET IDENTITY_INSERT B_tbl ON

Insert into B_tbl (uniqueid,Id)
select identityvalue,i.Id from inserted

SET IDENTITY_INSERT B_tbl OFF