Insert Values into an Identity Column

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted.

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)