How to Insert Values into an Identity Column in SQL Server
By Bill Graziano
on 06 August 2007
| 6 Comments
| 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.
