Fervent Coder

Coding Towards Utopia...by Rob Reynolds
posts - 278 , comments - 431 , trackbacks - 0

My Links

News


Rob Reynolds

Subscribe to Fervent Coder RSS
Subscribe to Fervent Coder by Email

About Me

I manage several open source projects. Need...
   ...package management for Windows?
   ...automated builds?
   ...database change management (migrations)?
   ...your application to check email?
   ...a monitoring utility?

I also write for



Like what you are reading? Want to buy me a cup of coffee?
PayPal - The safer, easier way to pay online!

Archives

Post Categories

Sites

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 on Saturday, August 23, 2008 7:11 PM | Filed Under [ Code ]

Feedback

Comments are closed.
Comments have been closed on this topic.

Powered by: