Insert values into identity column in SQL Server

Background

Identity column with auto increment in SQL Server data table does not allow for overriding its value. While in most cases it is the desired behavior sometimes there is a necessity to do things differently. For example while copying data from your development database to the production you may want to make sure all the IDs are exactly the same in both sets.

Solution

To enable overriding identity column it is required to run following command before executing the overriding script:

SET IDENTITY_INSERT table_name ON

You will need to replace table_name with the actual table name you want the identity insert enabled on.

After executing your command you may want to switch back to original settings, which you can achieve running:

SET IDENTITY_INSERT table_name OFF

To summarize, the whole SQL query overriding identity column should have following structure: 

SET IDENTITY_INSERT table_name ON

UPDATE table_name SET ...  --here goes your query

SET IDENTITY_INSERT table_name OFF