How to fix Cannot Insert Explicit Value For Identity Column in Table in SQL Server

While working on the SQL Server databases, we need to perform various operations like insert, update, and delete data from the update which are famously known as OLTP operations. In the Insert operation, we developers sometimes face the error message saying – ‘Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF in SQL Server’ and in this article, we will understand the error message, what is IDENTITY_INSERT and how to reproduce the error and then solve the error.

Explanation of the Error Message

The error message – “Cannot insert explicit value for identity column in table ‘table’ when IDENTITY_INSERT is set to OFF in SQL Server” appears while inserting the row in the table having identity column. So, when the user tries to insert the value of the identity column and IDENITY_INSERT is set to OFF then SQL Server says I am the one handling the identity column value, user can not interfere in this. So it throws the error.

What is IDENTITY_INSERT?

The identity column is the numeric column in the SQL Server table whose value increments automatically. The value of the identity column is set by the SQL Server itself based on the definition provided by the user. Thus user can not explicitly set the value of such columns and they are unique. The identity column can not be NULL. IDENITY_INSERT is the session level property which when set to “ON” the user can explicitly set/insert the value for the identity column.

Syntax for IDENTITY column:

IDENTITY [( seed, increment)]

Here SEED is the start of the numeric value for the column data and it is incremented with the INCREMENT values.

Reproduce the Issue

Step 1

Lets create the table name GeeksForGeeks which will store data for posts, author and engagements of the posts. And our need is to use the IDENTITY column because we don’t want to handle the value of the ID column and let the server handle it.

Query:

USE AdventureWorks2019

CREATE TABLE GeeksForGeeks
(ID int IDENTITY(1,1),
Post nvarchar(50),
Author nvarchar(50),
NumberOfComments int,
NumberOfLikes int
)

Output:

GeeksForGeeks table is created with Identity(1,1) column.

Explanation: We have used ID column as IDENTITY column with seed and increment is 1 each which means ID column value will start at 1 and will be incremented by 1 on each insert. So SQL Server will manage its value and as shown in the image we can see that table is created successfully.

Step 2

Now next step is to populate the data in our table so we will insert some random data about post, author and engagement which covers number of likes and comments on the posts. Here we can not explicitly inserting id column data and letting SQL Server to manage the column data.

Query:

INSERT INTO GeeksForGeeks (Post, Author, NumberOfComments, NumberOfLikes)
VALUES ('Post1','Aman', 10, 50)
INSERT INTO GeeksForGeeks (Post, Author, NumberOfComments, NumberOfLikes)
VALUES ('Post2','Gupta', 1, 5)
INSERT INTO GeeksForGeeks (Post, Author, NumberOfComments, NumberOfLikes)
VALUES ('Post3','Goyal', 5, 10)
INSERT INTO GeeksForGeeks (Post, Author, NumberOfComments, NumberOfLikes)
VALUES ('Post4','Deep', 100, 510)


SELECT * FROM GeeksForGeeks

Output:

We can see ID from 1 to 4 and we have not inserted these values.

Explanation: In this step, we have inserted 4 rows only and in the output window we can see 4 records inserted but important thing to notice is the value in the ID column, SQL server has automatically adjusted the value of the ID column based on the definition.

Step 3

Now suppose we got the need to skip some IDs (e.g we need to reserve them for some whatever reason) and directly insert 10th records with ID 10 so lets try this and reproduce our title error message.

Query:

INSERT INTO GeeksForGeeks VALUES
(10, 'POST10', 'Piyush', 30, 100)

Output:

We have reproduced the issue.

Explanation: We got the error while inserting the explicit value for the ID column which is 10 in our case. We can see that error message is slightly different bacause of the version (I am using SQL Sever 2019) but meaning is the same that we can insert explicit value only when IDENTITY_INSERT is ON and previous error message used to we can not insert explicit values when IDENTITY_INSERT is OFF. So we say that Microsoft has tried to make the message in a positive way but error is still the same.

So lets try to implement what SQL Server is trying to convey and set IDENTITY_INSERT to ON.

Step 4

Syntax to set identity insert is as below where we need to mention the table name and do note that this is session level property.

To set identity insert property ON, table is required which can be three part name, two or even one part is possible when same database is used.

SET IDENTITY_INSERT [TableName] ON/OFF

Query:

SET IDENTITY_INSERT GeeksForGeeks ON

INSERT INTO GeeksForGeeks 
VALUES
(10, 'POST10', 'Piyush', 30, 100)

Output:

Error is not resolved

Explanation: We are not able to solve the error yet as we got the same message because we missed the second part of the error which says we can explicitly insert the values only when column list is used. And thus we failed here.

Step 5

Now, we will make sure to solve the issue in this step. So we will first set IDENTIY_INSERT to ON and then mention all the columns list while inserting the data.

Query:

SET IDENTITY_INSERT GeeksForGeeks ON

INSERT INTO GeeksForGeeks (ID, Post, Author, NumberOfComments, NumberOfLikes )
VALUES
(10, 'POST10', 'Piyush', 30, 100)

SELECT * FROM GeeksForGeeks

Output:

Yaay! Issue resolved!

Explanation: Finally we solved the issue where we have set identity insert to ON and mentioned all the column list while inserting data and we have successfully inserted the 10th record in the table which can be seen in the output.

Step 6

But this does not end here. Now suppose we want to go back to our basic module which implicitly inserts data means we do not want to insert explicit data in the table and let the server handle it.

Query:

INSERT INTO GeeksForGeeks (Post, Author, NumberOfComments, NumberOfLikes)
VALUES ('Post11','Jain', 100, 510)

Output:

Now we got the error while implicit insertion.

Explanation: Now we are not able to insert data in the normal way which is implicit insertion and letting SQL Server handle the identity column value. But if we notice the error message it says can not insert when IDENTITY_INSERT is set to ON. So lets set it OFF and our issue will be resolved.

Step 7

As we have seen in above step that we need to set IDENTITY-INSERT to OFF. And check what will happen.

Query:

SET IDENTITY_INSERT GeeksForGeeks OFF

INSERT INTO GeeksForGeeks (Post, Author, NumberOfComments, NumberOfLikes)
VALUES ('Post11','Jain', 100, 510)

SELECT * FROM GeeksForGeeks

Output:

Now have inserted data and resolved our error too.

Explanation: When we set Identity insert to OFF we are able to insert the data with identity value which is automatically set by SQL Server. But interesting thing to note here is the value of the ID column, it is set to 11 because it takes the previous rows data to add the value in and in our case previous value is 10 and SQL Server has added the 1 to this value and thus ID is 11 for identity.

Conclusion

Encountering the “Cannot insert explicit value for identity column” error in SQL Server can be frustrating, but understanding its cause and how to address it can make the troubleshooting process much smoother. By leveraging the IDENTITY_INSERT option judiciously and following best practices for data manipulation, you can ensure the integrity and efficiency of your SQL Server databases.