36

I have a table that has a forced auto increment column and this column is a very valuable ID that is retained through out the entire app. Sorry to say it was poor development on my part to have this be the auto incrementing column.

So, here is the problem. I have to insert into this table an ID for the column that has already been created and removed from the table. Kind of like resurrecting this ID and putting it back into the table.

So how can I do this programatically do this without turning the column increment off. Correct me if I am wrong, if I turn it off programatically, It will restart at 0 or 1 and I don't want that to happen...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SpoiledTechie.com
  • 10,515
  • 23
  • 77
  • 100

4 Answers4

90

If you are in Microsoft SQL Server, you can "turn off" the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in:

  Set Identity_Insert [TableName] On
  -- --------------------------------------------
  Insert TableName (pkCol, [OtherColumns])
  Values(pkValue, [OtherValues])
  -- ---- Don't forget to turn it back off ------
  Set Identity_Insert [TableName] Off
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • Does it keep the last number for the incremented Identity? I don't want it to reset the entire Identity column it self... – SpoiledTechie.com Jun 26 '09 at 15:48
  • 1
    And @Scott, Yes, it "keeps" the old value... i.e., this prcoess does not affect the "current" value t obe used for the next auto-increment Identity... – Charles Bretana Jun 26 '09 at 15:54
  • I know this is an old thread but in SSMS 2012, it did NOT keep the old value. I followed the above example, inserted an id of 999999, turn the identity_insert "OFF", inserted a new record and it gave me 1000000, not the next number which should have been 72597. I'm investigating this further. – John Waclawski Dec 03 '14 at 20:06
  • 1
    It "Keeps" the old value only if the inserted value is less than the old value... I (incorrectly) assume that the purpose of doing this is to insert a value with an old, previously used but deleted, value needs to be re-inserted. – Charles Bretana Feb 09 '15 at 14:47
  • Note that You only must explicit columns in the SELECT clause of the query. – Xavier Sep 05 '17 at 14:59
9

In addition to Charles' answer (which is now 100% correct :-) and which preserves the current value of the IDENTITY on the table), you might also want to check the current value of an IDENTITY on a table - you can do this with this command here:

DBCC CHECKIDENT('YourTableName')

If you ever need to actually change it, you can do so by using this command here:

DBCC CHECKIDENT ('YourTableName', RESEED, (new value for IDENTITY) )
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
6

Actually, the code above for INDENTITY_INSERT is correct - turning it ON tells the server you want to insert the values yourself. It allows you to insert values into an IDENTITY column. You then want to turn it back off (allowing the server to generate and insert the values) when you are done.

Donald Lancaster
  • 61
  • 1
  • 1
-1
bulk insert tablename from 'C:\test.csv' with (rowterminator = '\n',fieldterminator = ',',KEEPIDENTITY)
Drishya Nair
  • 1
  • 3
    Please explain why this answer solves the problem. Read [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) – Matthijs Mar 30 '19 at 10:43