Inserting values into an identity column

Introduction

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 columnCREATE TABLE #IDENTESTTABLE 
(ROWNO_COL INT IDENTITY(1,1),VALUE_COL VARCHAR(15))GO--Inserting a row into the tableINSERT #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 1Cannot 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 ONINSERT #IDENTESTTABLE(ROWNO_COL, VALUE_COL) 
VALUES (1, 'FIRST ROW')SET IDENTITY_INSERT #IDENTESTTABLE OFF.

 More fun with IDENTITY here. Enjoy the code snippet.