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:
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
http://www.exacthelp.com/2012/04/cannot-insert-explicit-value-for.html?showComment=1471409109694#c5838968252069226129
Post a Comment