Jun 2, 2013

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. 

2 comments:

  1. It is useful to learn how to set and reset a error.Thank you author for posting this kind of error.

    http://www.wikitechy.com/fix-error/error-cannot-insert-explicit-value-for-identity-column-in-table


    Both are really good.
    Cheers,
    Venkat

    ReplyDelete
  2. http://www.exacthelp.com/2012/04/cannot-insert-explicit-value-for.html?showComment=1471409109694#c5838968252069226129

    ReplyDelete