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
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 INTO 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
INTO IdentityTable(TheIdentity, TheValue) VALUES (10, 'Row Ten')
SET IDENTITY_INSERT IdentityTable OFF
INSERT
INTO 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)
