How to Insert Values into an Identity Column in SQL Server

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But what if you want to insert your own value into the column? It's actually very easy to do.

The trick is to enable IDENTITY_INSERT for the table. That looks like this:


SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF


Here are some key points about IDENTITY_INSERT

* It can only be enabled on one table at a time. If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error.
* When it is enabled on a table you must specify a value for the identity column.
* The user issuing the statement must own the object, be a system administrator (sysadmin role), be the database owner (dbo) or be a member of the db_ddladmin role in order to run the command.

Read the full article for more information. For instance the fact that this can modify the identity properties of your column depending on the values you update the table with.