Monday, April 8, 2013

SQL Execution Error: Cannot insert explicit value for identity column in table.....


Sometimes we get a very interesting error when we try to insert a new row in a table. The error is like following:

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

Then we developers starts googling and find almost same solution. 


SET IDENTITY_INSERT TableName  On
insert into TableName  Values(1,1,1)
SET IDENTITY_INSERT TableName  OFF


This solution is ok. But before going to that solution we need to know in which context we have to set identity_insert on. 

The context is if we want to insert data to an Identity Specified column. (Generally we specify the primary key column identical.)


So, do we want to insert the data to a Identity column? If answer is “No” then check your sql query. I have seen many beginners do this wrong in their sql query. 

So, Happy Coding.

No comments:

Post a Comment