Making primary key and identity column after data has been loaded.

Question

I have quick question for you SQL gurus. I have existing tables without primary key column and Identity is not set. Now I am trying to modify those tables by making existing integer column as primary key and adding identity values for that column. My question is should I first copy all the records from the table to a temp table before making those changes . Do I loose all the previous records if I ran the T-SQL commnad to make primary key and add identity column on those tables. What are the approaches should I take such as

1) Create temp table to copy all the records from the table to be modified 2) Load all the records to the temptable 3) Make changes on the table schema 4) Finally load the records from the temp table to the original table.

Or

there are better ways that this? I really appreciate your help

Thanks

Answer 1

If your existing integer column is unique and suitable, there should be no problem converting it to a PK.

Another alternative, if you don't want to use the existing column, you can add a new PK columns to the main table, populate it and seed it, then run update statements to update all other tables with new PK.

Whatever way you do it, make sure you do a back-up first!!

Answer 2

just do all of your changes in management studio, copy/paste the generated script into a file. DON'T SAVE CHANGES at this point. Look over and edit that script as necessary, it will probably do almost exactly what you are thinking (it will drop the original table and rename the temp one to the original's name), but handle all constraints and FKs as well.

Answer 3

Tools>Options>Designers>Table and Database Designers

Uncheck "Prevent saving changes that require table re-creation"

[Edit] I've tried this with populated tables and I didn't lose data, but I don't really know much about this.

Answer 4

You can always add the IDENTITY column after you have finished copying your data around. You can also then reset the IDENTITY seed to the max integer + 1. That should solve your problems.

DBCC CHECKIDENT ('MyTable', RESEED, n)

Where n is the number you want the identity to start at.

Answer 5

Hopefully you don't have too many records in the table. What happens if you use Management studio to change an existing field to identity is that it creates another table with the identity field set. it turns identity insert on and inserets the records from the original table, then turns identity insert off. Then it drops the old table and renames the table it just created. This can be quite a lengthy process if you have many records. If so I would script this out and then do it in a job that runs during the off hours because the table will be completely locked while you do this.