Changing field to identity

[hmckillop]

I have to change a integer field to be an identity. This isnt a big problem, the issue is I have 65 Million rows in the table and need the most efficient way of changing this over. The table has 3 indexes (inc clustered)
I have tried BCPing the data out, truncating tha table, amending the field structure and then repopulating via bcp but I am getting PK_Violation on insert.
I have tried using the script build by SQL Enterprise which basically creates atemp table of the correct structure, inserts into it and then rebuilds the indices - this takes about 7 hrs.

If anyone has done anything similiar and found the most efficient way, any help much appreciated.
Thanks


"I'm living so far beyond my income that we may almost be said to be living apart

 

[donutman]

This is a perfect application for this thread's technique: Updating Rows with Row Count
thread183-980264 (assuming that you have a suitable column to use for sorting and that you have enough space to create a 2nd table of equal size).
Don't update rows, but rather insert them into a new table in groups.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]