How to Fix ‘Can’t insert explicit value for identity column in table’

The primary key column is often set to auto-increment when constructing a SQL Server database. To achieve this, the IDENTITY property is enabled on the primary key column. The starting value and increment step are specified as parameters for this property. Each time a new record is added and IDENTITY_INSERT is set to OFF, the IDENTITY column value increases by the predefined step, which is usually a number. Additionally, the IDENTITY INSERT property can only be set to ON for one table at a time within a single session.

This article will discuss the error “Cannot insert explicit value for identity column in table <table name> when IDENTITY_INSERT is set to OFF.” This error typically appears when you attempt to manually insert a value into an identity column while IDENTITY_INSERT is OFF.

Creating the Database and Table:

To illustrate, first, create a database named “appuals.”

Next, create a table named “person” using the following code. This table includes a primary key column with an identity property:

CREATE TABLE person
  (
    ID INT IDENTITY (1, 1),
    first_name VARCHAR(MAX),
    last_name VARCHAR(MAX)
  )

Setting the IDENTITY_INSERT Option:

To handle the error, learn to set the IDENTITY_INSERT option as shown below. This feature allows you to specify whether an explicit value can be inserted into an identity column:

SET IDENTITY_INSERT <database name>. <schema name>. <table name> { OFF | ON }

The first argument, <database name>, is the name of the database containing the table. The second argument, <schema name>, is the schema to which the table belongs. The third argument, <table name>, specifies the table with the identity column in question.

There are two primary methods to insert data into a table without errors. Both methods serve as solutions to the error described earlier.

Error 1: IDENTITY_INSERT Set to OFF

If you attempt to insert data with the IDENTITY_INSERT set to OFF, including a value for the identity column in the INSERT statement will trigger the error: “Cannot insert explicit value for identity column in table ‘person’ when IDENTITY_INSERT is set to OFF.”

Execute this code in the query tab:

SET IDENTITY_INSERT person OFF;
INSERT INTO person(ID, first_name, last_name)
VALUES (3, 'Sadia', 'Majeed'),
       (4, 'Michel', 'Ronald')

This will result in the following error:

Solution:

When IDENTITY_INSERT is OFF, exclude the identity column from the INSERT statement.

Execute this alternative code in the query tab:

SET IDENTITY_INSERT person OFF;
INSERT INTO person(first_name, last_name)
VALUES ('Sadia', 'Majeed'),
       ('Michel', 'Ronald')

This will successfully insert data without error. The IDENTITY property automatically generates a unique primary key ID, as shown below.

Error 2: IDENTITY_INSERT Set to ON

If you omit the ID value while IDENTITY_INSERT is set to ON, you’ll receive the error: “Msg 545, Level 16, State 1, Line 17. An explicit value must be specified for the identity column in table ‘person’ when IDENTITY_INSERT is set to ON.”

Solution:

When IDENTITY_INSERT is ON, you must explicitly include the primary key ID in the INSERT statement.

Execute this code in the query tab:

SET IDENTITY_INSERT person ON;
INSERT INTO person(ID, first_name, last_name)
VALUES (5, 'Jack', 'Black'),
       (6, 'John', 'Wicky')

This will insert data without error, requiring the user to specify the primary key ID directly. It will not auto-generate as it does when IDENTITY_INSERT is OFF.

When you SET IDENTITY_INSERT ON, it remains active for the entire session, allowing multiple records to be inserted. This setting is session-specific, so opening another query tab requires enabling it again for that window.