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.

First we'll need a table to work with.  My examples will use this table:

use tempdb
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
 DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
 TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
 TheValue NVARCHAR(20) NOT NULL )
GO

Simply trying to INSERT a value into the identity column generates an error:

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in 

table 'IdentityTable' when IDENTITY_INSERT is set to OFF.

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.

If you insert a value greater than the current identity seed SQL Server uses the value to reset the identity seed.  For example:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')

SET IDENTITY_INSERT IdentityTable OFF

INSERT IdentityTable(TheValue)
VALUES ('Should be 11')

SELECT * FROM IdentityTable
GO

(1 row(s) affected)

(1 row(s) affected)
TheIdentity TheValue
----------- --------------------
         10 Row Ten
         11 Should be 11

(2 row(s) affected)