Resolve "Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF" in SQL Server

Home > Search
  by

When attempting to perform an INSERT in SQL Server, message Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF appears. For the purpose of this article, let's say we get this error when performing the following insert.

INSERT INTO Table 
(ID, Column1)
VALUES
(3, 'data');

 

The fix for this is easy. We simply turn on IDENTITY_INSERT, insert the data, and then turn off IDENTITY_INSERT.

SET IDENTITY_INSERT Table ON

INSERT INTO Table 
(ID, Column1)
VALUES
(3, 'data');

SET IDENTITY_INSERT Table OFF 

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments