By default, the SQL server won’t allow you to explicitly insert a value for an identity column. But I was surprised to see a requirement that I got from my client. There is no need to insert an explicit value for the identity column as it is auto-generated and auto-incremented. You just need to provide the explicit values for all other columns except the Identity column in your INSERT statement.
To insert data into the table with an identity column in SQL Server, you just need to set the IDENTITY_INSERT to ON and then run the insert query.
If you try to insert an explicit value for an identity column in the SQL server, you will receive the error below. Check out the screenshot below for your reference.
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.
If you still need to insert an explicit value for the Identity column, follow the below steps.
Check out: An explicit value for the identity column in table
Before inserting the query, run the query below to set the IDENTITY_INSERT to ON. Check out the screenshot below for your reference.
SET IDENTITY_INSERT Orders ON;
Now, let’s run the Insert query below. You will see that the record has been inserted successfully.
INSERT INTO Orders (OrderID, OrderName, OrderType) VALUES (3,'Medicine', 'Health');
To cross-check if the Record has been inserted successfully, Let’s run the Select statement below. The screenshot below shows that the record has been inserted successfully.
Check out: How To Add Identity Column To Existing Table In SQL Server
Select * from Orders
Now, you can turn off the explicit insertion into the table with the identity column with the below query.
SET IDENTITY_INSERT Orders OFF;
Conclusion
Ideally, it is not a good idea to insert an explicit value for an identity column in an SQL server as it is an autogenerated column. But if you have a requirement, you can use the above information to insert an explicit value for an identity column in a table.
You may also like following the articles below
- How to create a table with identity column in SQL Server
- How to turn off identity column in SQL Server
Grey is a highly experienced and certified database expert with over 15 years of hands-on experience in designing, implementing, and managing complex database systems. Currently employed at WEX, USA, Grey has established a reputation as a go-to resource for all things related to database management, particularly in Microsoft SQL Server and Oracle environments. He is a Certified Microsoft SQL Server Professional (MCSE: Data Management and Analytics) and Oracle Certified Professional (OCP), with Extensive database performance tuning and optimization knowledge and a proven track record in designing and implementing high-availability database solutions.