How to Insert Values into an Identity Column in SQL Server

By Bill Graziano on 6 August 2007 | Tags: Identity , INSERT


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)

We have another article on understanding identity columns that covers resetting an identity seed and determining what value was inserted.  You can also see a list of all our articles that cover identity columns.


Related Articles

Fast CSV Import in PowerShell to SQL Server (18 March 2014)

Efficiently Reuse Gaps in an Identity Column (9 February 2010)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

Using Views to Enforce Business Rules (9 April 2007)

Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts (14 August 2006)

Anticipating Primary Key Violations (23 November 2003)

Using EXISTS (12 October 2003)

Understanding Identity Columns (9 March 2002)

Other Recent Forum Posts

Performance tuning (79m)

As I gain experience and get older, I'm working much slower, but producing better quality, but (5h)

Master DB 2019 problem (19h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

- Advertisement -