inserting value into identity column

• vaithi.saran846

  SSC Eights!

  Points: 992

  More actions

  November 22, 2011 at 1:34 am

  #248321

  Hi,

  I have a table with identity column and i have data in identity column from 1 to 10 and 20 to 50. 11 to 19 are deleted. if i insert a row into this table. i will get the identity as 51. But i need to insert it as 11. Can anybody help on this.

  Regards,

  Vaithilingam.K

• shashank-666535

  Old Hand

  Points: 348

  More actions

  November 22, 2011 at 1:57 am

  #1411264

  Yes it can be done by setting IDENTITY_INSERT ON

  http://msdn.microsoft.com/en-us/library/ms188059.aspx

• KrishDBA

  Right there with Babe

  Points: 715

  More actions

  November 22, 2011 at 2:31 am

  #1411277

  Please follow the below steps:

  1) SET IDENTITY_INSERT ON

  2) insert the values using a INSERT Sql.

  3) SET IDENTITY_INSERT OFF

  Do remember to turn off the identity_insert to OFF again.

• Johan Bijnens

  SSC Guru

  Points: 135376

  More actions

  November 22, 2011 at 2:39 am

  #1411281

  Keep in mind columns with the identity property are meant to generate their own value in a given sequence.

  (check books online for seed and increment)

  However - as replied by the others - , using set identity insert for a given table is an acceptable solution to support a load.

  After such load, dbcc checkident should be ran so the engine knows which value to generate next.

  Check books online DBCC CHECKIDENT for the options and usage info.

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me