Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

How to Insert Values into an Identity Column in SQL Server

April 1 2009 , Written by Balavardhan Reddy Published on #SQL Server2005

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 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)

Share this post

Repost 0

Comment on this post