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]
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
