Sunday, September 16, 2012

Error: Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF

I run into situation where i have to insert explicit value for identity column and run into following error, which is expected. Incase you run into similar situation here is the solution to insert value into Identity column in SQL Server.

Error: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Users' when IDENTITY_INSERT is set to OFF.

Cause of error: Trying to insert record including ID field where ID field is IDENTITY Column.

Solution: 
1) Make sure that you are in situation where you really want to insert ID, If that is not the requirement than simply remove ID field from your insert statement and try to execute the insert statement again.

2) Since i was sure that i want to insert ID in Identity column, here is solution...

SET IDENTITY_INSERT YourTableName ON

INSERT INTO YourTableName
(IDENTITY Column, Column1...ColumnN)
VALUES
(IDENTITY Value, Value1, ...., ValueN)

SET IDENTITY_INSERT YourTableName OFF

Note:
Make sure that your insert statement does include all column name list, otherwise you will run into this error.

Error: 
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Users' can only be specified when a column list is used and IDENTITY_INSERT is ON.


Wrong Insert Statement
INSERT INTO YourTableName
VALUES
(IDENTITY Value, Value1, ...., ValueN)

Correct Insert Statement
INSERT INTO YourTableName
(IDENTITY Column, Column1...ColumnN)
VALUES
(IDENTITY Value, Value1, ...., ValueN)

No comments:

Most Recent Post

Subscribe Blog via Email

Enter your email address:



Disclaimers:We have tried hard to provide accurate information, as a user, you agree that you bear sole responsibility for your own decisions to use any programs, documents, source code, tips, articles or any other information provided on this Blog.
Page copy protected against web site content infringement by Copyscape