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. The IDENTITY limit is set on for the primary key column to do this. The starting location and step of increment are transferred to the IDENTITY column as parameters. Then whenever a new record is added, and the identity insert is set to OFF, the value of the IDENTITY column is increased by the pre-defined step normally a number. Moreover, the IDENTITY INSERT property is set to ON only for one table in a single session.

In this article, we will discuss the error “Cannot insert explicit value for identity column in table <table name> when IDENTITY_INSERT is set to OFF” as shown below.

The error arises when the user has set “identity_insert” to “OFF”. Then tries to insert data into the primary key column of the table explicitly. This can be explained using the example below.

Database and table creation:

First, create a database named “appuals”.

Create a table named “person” using the following code. Built table using a “PRIMARY KEY IDENTITY”

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

Syntax for setting “identity_insert off | on”:

The “set identity_insert off | on” will help us resolve this error. The correct syntax for this statement is as below.

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

Whereas the first argument <database name> is the name of the database in which the table is located. The second argument <schema name> shows is the schema to which that table belongs whose identity value has to be set to ON or OFF. The third argument <table name> is the table with the identity column.

There are fundamentally two different ways of data insertion into the table without error. These are considered as the solution to this error and are discussed below.

Error 1:  Set identity_insert OFF

In the first case, we will insert data into the table with the “IDENTITY INSERT” set to “OFF”. So, if the ID is present into the INSERT statement, you will get the error “Cannot insert explicit value for identity column in table ‘person’ when IDENTITY_INSERT is set to OFF”.

Execute the following 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')

The output will be like this.

Solution:

When turning the “IDENTITY INSERT OFF”, the “PRIMARY KEY ID” MUST NOT be PRESENT into the insert statement

Now execute the following code in the query tab

 set identity_insert person off;
 insert into person(first_name,last_name)
 values('Sadia','Majeed'),
 ('Michel','Ronald')

This will insert data into the table without an error. Moreover, The PRIMARY KEY ID is not required to be inserted by the user, rather it will add unique ID value automatically as seen in the figure below.

Error 2:  Set identity_insert ON

In the second case, we will insert data into the table with the “IDENTITY INSERT” set to “ON”. So, if the ID is not present into the INSERT statement, you will get the error ” Msg 545, Level 16, State 1, Line 17. The explicit value must be specified for identity column in table ‘person’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column”.

Solution:

When turning the “IDENTITY INSERT ON” the “PRIMARY KEY ID” must be present in the insert statement.

Execute the following 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 into the table without an error. Thus The PRIMARY KEY ID is explicitly required to be inserted by the user. Also, it will not add unique ID value automatically as seen in the figure below.

If you “SET IDENTITY INSERT ON”, it will remain on for the whole session. Thus you can add as many records as you want once this has been set. This also refers only to the session where it is enabled. So if you open another query tab you need to turn it ON again for that query window.