How to Insert Values into an Identity Column in SQL Server

[admin]

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:

Code Select Expand

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:

Code Select Expand

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:

Code Select Expand

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:

Code Select Expand

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)

[admin]

The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1.

Code Select Expand

DBCC CHECKIDENT('Customer', RESEED, 0)