Have Geek Brain

Create primary key and id after loading data

I have a quick question for you SQL guru. I have existing tables with no primary key column and Identity is not set. Now I am trying to modify these tables by creating an existing integer column as the primary key and adding ID values ​​for that column. My question is, should I first copy all records from the table to a temporary table before making these changes. Free up all previous records if I ran commnad T-SQL to make the primary key and add the identity column in those tables. Which approaches should be used, e.g.

1) Create a temp table to copy all records from the table to be modified 2) Load all records into tempting 3) Modify the table schema 4) Finally, load the records from the temp table into the original table.

Or

Are there any better ways to do this? I really appreciate your help.

thanks

+1


a source to share


5 answers


Hope you don't have many entries in the table. What happens if you use Management Studio to modify an existing identity field is that it creates another table with the identity field set. it includes the id and inserts records from the original table and then disables the id. Then it removes the old table and renames the newly created table. This can be a lengthy process if you have a lot of records. If so, I would script and then do it in a job that runs out of business hours because the table will be completely locked while you do this.



+3


a source


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 haven't lost any data, but I don't know much about that.

+4


a source


just do all your changes in control studio, copy / paste the generated script into the file. DO NOT SAVE CHANGES for now. Review and edit the script as needed, it will probably do almost what you think (it will lose the original table and rename the temporary copy to its original name), but it also handles all constraints and FKs.

+2


a source


If your existing integer column is unique and appropriate, there should be no problem converting it to PC.

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

Whichever way you do it, make sure you back it up first!

0


a source


You can always add an IDENTITY column after copying the data. You can also reset the IDENTITY seed to the maximum number + 1. This should fix your problems.

CHCCKIDENT DBCC ("MyTable", RESEED, n)

Where n is the number from which you want to start identification.

0


a source






More articles:


All Articles