Inserting values into an identity column

Friday, 19 June 2009

The identity column gets value assigned automatically when a new records inserted into the table.

Its possible to insert a value into an identity column by setting the IDENTITY_INSERT to ON.

Lets look at the following example,

--Test table with an identity column

CREATE TABLE #IDENTESTTABLE

(

ROWNO_COL INT IDENTITY(1,1),

VALUE_COL VARCHAR(15)

)

GO

--Inserting a row into the table

INSERT #IDENTESTTABLE(ROWNO_COL, VALUE_COL) VALUES (1, 'TEST VALUE')

The above insert statement would terminate with the following error,

Msg 544, Level 16, State 1, Line 1

Cannot insert explicit value for identity column in table '#IdenTestTable____000000000004'

when IDENTITY_INSERT is set to OFF.

To avoid this error set IDENTITY_INSERT to ON.Here we go,

SET IDENTITY_INSERT #IDENTESTTABLE ON

INSERT #IDENTESTTABLE(ROWNO_COL, VALUE_COL)

VALUES (1, 'FIRST ROW')

SET IDENTITY_INSERT #IDENTESTTABLE OFF.

More fun with IDENTITY here