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