Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KlausEngel
Yak Posting Veteran
85 Posts |
 Posted - 2010-06-17 : 15:21:41
|
| I have a table with about 30,000 records and I would like to add a column to the existing table that gives each entry a unique ID. Can I still do this after the fact, and, if so, how?Thank you |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
KlausEngel
Yak Posting Veteran
85 Posts |
Posted - 2010-06-17 : 15:42:33
|
quote:
Originally posted by tkizer
Will the unique ID be an identity column?
Yes it should be an identity column. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-06-17 : 15:52:25
|
| You can still do it after the fact, but it will require the table to be rebuilt.It's easiest to write the script by letting Management Studio do the work for you. Edit the table by adding your column (possibly reposition the ID column so that it's first in the table), and then click the button to generate the script for you. SSMS does the right thing here. It'll create a new object with the layout you want, move the data over to that table, move indexes and other objects such as constraints, drop the old table, and then rename the new table. You can write the code yourself, but it's quite tedious. Just let SSMS do the hard work. You could also let SSMS save the table with the new layout, but I always just grab the script and run that myself. I do it this way so that I can put the script in source control so that this is repeatable in all of my environments.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
add auto incrementing couln to a table