April 21, 2005 at 8:13 am
I'm looking for a script that completely rebuilds the identity value of a table.
We append several (hundred of)thousands of records from different sources into one overlapping table.
Because we need to get rid of all duplicates, but keep the records that have been imported earlier we use identity column. (and delete all duplicate records having a higher ID than the lowest id for each duplicate record)
After all duplicates have been discarded we would like to rebuild the identity from 1 to x (so the max record = max identity)
Dropping the column and adding again is a possibility, but seems like overkill.
Tried DBCC CHECKIDENT (
Colin van Viegen
April 21, 2005 at 8:36 am
Why do you think it's an overkill? If you can do so, I think that is the easiest solution. Anyway, CHECKIDENT won't help you much here, as it will only reseed the next to be assigned IDENTITY value and doesn't rebuild already existing values.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 25, 2005 at 7:40 am
How about selecting every column apart from the identity cloumn into a temp table, inserting into the temp table using a SELECT DISTINCT, truncating the main table then inserting the contents of the temp table into the main table?
Your 2 insert statements will have to specify every column apart from the identity column and if the table is very wide the staement may take a long time to run but I can't see another way to do it.
April 25, 2005 at 8:08 am
Thank guys for your feedback.
It seems Microsoft SQL Server has no direct answer for this issue, rather than drop and add the ID column again. (just like frank suggested)
It's also the fastest way. but seems somewhat strange to completely drop a column, and then adding the same column again. (especcially when there are almost 7 million records in this table.)
Colin van Viegen
April 25, 2005 at 8:31 am
And to be honest I don't think there is any guarantee that you will get those ID in the correct (expected) order. To be on the safe side I would create that extracolumn empty at temp table creation time and update the values on it after the delete. That way you are sure that the record is getting the right ID
Just my $0.02
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply