LinkBack URL
About LinkBacksHi All,
Is there a way to reset the an autonumberID in sql2000? if so, will it change existing records or do I need to delete data then reset then populate table?
Thanks!
Hi All,
Is there a way to reset the an autonumberID in sql2000? if so, will it change existing records or do I need to delete data then reset then populate table?
Thanks!
This works in 2005, and probably in 2000 as well:
DBCC CHECKIDENT('table', RESEED,0)
The existing records are not affected, so you would need to either update the Key values or deleted the records.
[edit] I should point out because it wasn't clear, that this will reseed the identity value regardless of whatever is in the table already. So, if your identity field is the primary key (and more are), the next insert will probably fail on a primary key violation (assuming that PK=1 is already in the table). An identity field itself does NOT ensure that the values will be unique.
Also, you can not UPDATE the values in the identity field directly. If the records need to be preserved, they should be copied to a temp table and then inserted back once the identity has been reseeded.[/edit]
Last edited by Wolffy; September 18th, 2009 at 05:09 PM.
Wolffy
.-- ----- ..-. ..-. -.--
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.
that gave me a heartattack...hehe...
deleted data from table first then run that in sql analyzer and then added data back into table...w00t w00t...all is good.
Posting Permissions 
Bookmarks