Cannot insert explicit value for identity column in table

by

Recently, I tried force a value into the identity column, SQL Server stoped me with the error Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.

Cannot insert explicit value for identity column in table

I was executing the following query.

SQL

-- This will throw an error!
INSERT INTO dbo.EmployeesN (EmployeeID, FirstName, LastName)
VALUES (99, 'Michael', 'Johnson');

After executing the query above, I got the error shown in the screenshot below.

Cannot insert explicit value for identity column in table

The Error Message:

Cannot insert explicit value for identity column in table ‘EmployeesN’ when IDENTITY_INSERT is set to OFF.

Solution

The Override: IDENTITY_INSERT

To fix this error, you must toggle a session-level setting called IDENTITY_INSERT.

SQL

First, execute the query below to turn on IDENTITY_INSERT property.

-- 1. Turn on identity insertion
SET IDENTITY_INSERT dbo.EmployeesN ON;

After executing the above query, I got the expected output as shown below.

cannot insert explicit value for identity column in table when identity_insert is set to off

Now, reexecuted the above query and this time the query has been executed successfully as shown below.

cannot insert explicit value for identity column in table sql server

Finally, turn it back off immidiately after inserting the record using the following query.

-- 3. Turn it back off immediately
SET IDENTITY_INSERT dbo.EmployeesN OFF;

After executing the above query, I got the expected output as shown below.

sqlexception cannot insert explicit value for identity column in table

Pro Tip: Only one table in a session can have IDENTITY_INSERT set to ON at a time. Always turn it off immediately after you are done.

You may also like the following articles: