CODEWITHSUNDEEP
sqlsql-serverdatabasessmsdatabase-replication
agm92
agm92

Reputation: 364

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

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.

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.

Upvotes: 14

Views: 71061

Answers (4)

Supercoder
Supercoder

Reputation: 1204

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

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

Upvotes: 15

Vadim Berman
Vadim Berman

Reputation: 2032

Just to add one possible gotcha for people who like me did everything by the book and still got the message.

Check for triggers on insert. They may be attempting to create more rows while executing code that does not have the explicit identity column values.

If it's the case, disable the trigger (or drop and recreate it).

Upvotes: 4

Adarsh H D Dev
Adarsh H D Dev

Reputation: 618

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

Upvotes: 3

bmsqldev
bmsqldev

Reputation: 2735

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

Upvotes: 6

Related Questions