"Turning on" identity after an import?

[gbaughma]

I have a huge table that I have imported into SQL Server 2008 from Access (about 1.6M Records).

Now, I need to make the first column an "Identity" column, starting at the next record number and incrementing by 1.

Of course, if I just change it in the table design, it times out....

Any thoughts on this? I need to preserve the current identity numbers (because it's an index that links to other tables), but for future inserted data, it needs to auto-increment.

TIA!


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[markros]

Take a look at the similar thread here thread183-1548439

PluralSight Learning Library

 

[gbaughma]

That doesn't really answer the problem; the issue is that I need to KEEP the existing PK (which was an autonumber in Access)... switch that to an identity, and seed the identity with the next "autonumber" number so that it continues.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[SQLSister]

OK this is what you should do, create a new table with the identity field set up as the PK and the same structure.
Then run this code
set identity_insert mytable on
then import the records from the current table setting your current Access id to the field you have named as the identity field (you may need to do this in batches iif it takes too long
the run Set identity_insert off
Then drop the old table and rename the new one.
Create the indexes you want on the new table.

Do everything with a script. Never use the GUI for this type of thing.

"NOTHING is more important in a database than integrity." ESquared