An Explicit Value For The Identity Column In Table

Recently, while trying to insert explicit values into an existing table, I got this error: “An explicit value for the identity column in table can only be specified when a column list is used.” You can see the same error in the screenshot below.

Cause Of the Error

This error occurs because IDENTITY_INSERT is set to OFF by default for the table that contains the identity column. Therefore, explicit values for the identity column are not allowed.

Solution

Now, to solve this error, we need to follow the below steps.

1. First, we must execute the SQL query below to set the IDENTITY_INSERT to ON, as shown in the screenshot below.

SET IDENTITY_INSERT Orders ON;

Check out How To Insert Values Into Table With Identity Column In SQL Server

2. Now, we can run the insert query to insert the data. This time, the data was inserted successfully without any issues. Check out the screenshot below for more clarity.

Now, we can cross-check whether data exists in the table, and as shown in the screenshot below, the data is available in the table.

Check out How To Turn Off Identity Column In SQL Server

3. As a best practice, we can execute the query below to set the IDENTITY_INSERT to Off, as shown in the screenshot below.

SET IDENTITY_INSERT Orders OFF;

Conclusion

As discussed in this article, you can fix this error by setting the IDENTITY_INSERT to ON.

You may also like the following articles below.

• Saving Changes Is Not Permitted SQL Server