<< Happy Birthday Randy! | Home | Magic Windows Trick: Query and Reset Terminal Sessions >>

Inserting into Microsoft SQL Server Database Identity Columns

This came up in a discussion the other day so I thought I would share it. 

 

CREATE TABLE dbo.TestIdentity 
( 
      ID bigint identity(1,1) --primary key
      ,[Desc] char(20) 
)
 
 
INSERT INTO dbo.TestIdentity ([Desc]) values ('first')
INSERT INTO dbo.TestIdentity ([Desc]) values ('second')
 
SET IDENTITY_INSERT TestIdentity ON
 
--note the identities being inserted
INSERT INTO dbo.TestIdentity (ID,[Desc]) values (1200,'id-third')
INSERT INTO dbo.TestIdentity (ID,[Desc]) values (1,'id-fourth') -–this works until you set a unique constraint on the column.
 
 
SET IDENTITY_INSERT TestIdentity OFF
 
INSERT INTO dbo.TestIdentity ([Desc]) values ('third')
INSERT INTO dbo.TestIdentity ([Desc]) values ('forth')
 
SELECT * FROM dbo.TestIdentity
/*
 * You can only turn on IDENTITY_INSERT for one table per session so it's always a good idea to turn it off when you're done with it.
*/
 
DROP TABLE dbo.TestIdentity
 

Note that you can re-insert the same Id into an identity column unless it has a unique constraint on it, so limit it's use! Also note that when you turn off identity insert, it automatically re-seeds the identity at the next value after the HIGHEST value in the column.

Print | posted @ Saturday, August 23, 2008 7:11 PM

Comments on this entry:

No comments posted yet.

Your comment:

Title:
Name:
Email:
Website:
 
Italic Underline Blockquote Hyperlink
 
 
Please add 5 and 2 and type the answer here:
 
Twitter