Cannot insert explicit value for the identity column '' in the target table '' of the INSERT statement when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Suppose we have two tables, tblSource and tblTarget as follow:

CREATE TABLE tblSource(

     EmpId BIGINT  PRIMARY KEY,

     EmpName VARCHAR(100),

     Active BIT

)

CREATE TABLE tblTarget(

     EmpId BIGINT IDENTITY PRIMARY KEY,

     EmpName VARCHAR(100),

     Active BIT

)

Now if we will execute following sql query:

SET IDENTITY_INSERT tblTarget ON

INSERT INTO tblTarget(EmpId,EmpName,Active)

SELECT EmpId,EmpName,Active

FROM(

     INSERT INTO tblsource(EmpId,EmpName,Active)

     OUTPUT INSERTED.EmpId,INSERTED.EmpName,INSERTED.Active

     VALUES(1,'Scott',1)

) tbl

We will get error message like:

Cannot insert explicit value for the identity column '' in the target table '' of the INSERT statement when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.

Cause: We cannot insert value to identity column from nested insert statement.

Solution:

For this we have to drop identity property from tblTarget.