Microsoft SQL Server - Resolve "Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF"
by
Jeremy Canfield |
Updated: March 17 2020
| Microsoft SQL Server articles
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
Did you find this article helpful?
If so, consider buying me a coffee over at 