An Explicit Value For The Identity Column In Table

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

Fix “An Explicit Value For The Identity Column In Table”

The below screenshot shows the same error.

Error Cause

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

Solution

To solve this error, follow the steps below.

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 SQL Server Trigger If Exists Raise Error

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 execute the below query to cross check if the data is available in the table as shown in the screenshot below.

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 an explicit value for the identity column in table can only be specified by setting the IDENTITY_INSERT property value to ON.

You may also like the following articles below.

• Error: 40 – could not open a connection to sql server
• Cannot find type [microsoft.sqlserver.management.smo.server]