| Author |
Topic |
|
irey
Starting Member
3 Posts |
 Posted - 2008-11-24 : 05:32:25
|
| Hello everyone,I need to replace a column in an already existing table with an autoincrementing id. At the moment this column is a varchar and it contains garbage, so dropping it's data is ok as long as the rest of the table is preserved.I made a test table to try a possible solution (left the original one untouched). An "alter table" command successfully converts varchar to int, but how can I replace old and null values with an autogenerated id?I tried issuing an update command setting this value to rank() over (order by some_other_column asc) but sql server answers that the rank function is not allowed in an update operation. What else can I do?Thanks in advance |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-24 : 05:38:37
|
quote:
Originally posted by irey
Hello everyone,I need to replace a column in an already existing table with an autoincrementing id. At the moment this column is a varchar and it contains garbage, so dropping it's data is ok as long as the rest of the table is preserved.I made a test table to try a possible solution (left the original one untouched). An "alter table" command successfully converts varchar to int, but how can I replace old and null values with an autogenerated id?I tried issuing an update command setting this value to rank() over (order by some_other_column asc) but sql server answers that the rank function is not allowed in an update operation. What else can I do?Thanks in advance
SELECT IDENTITY(int, 1,1) AS ID_NumINTO NewTableFROM OldTable |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-24 : 05:41:22
|
| Put the column names that you need in the select part of the above, and ignore the ones that you don't need. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-24 : 05:44:30
|
Or, You could alter your table too.Something like,ALTER TABLE youtable ADD id_column INT IDENTITY |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-24 : 05:44:46
|
| you cant directly modify a column to be of auto incrementing type. however you could create a new identity column, drop your current int column and then rename the newly created column as your current one using sp_rename. Make sure you script out any constraints on current column and apply it to new column after rename. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-24 : 05:48:14
|
| ALso, you can use rank to update a field. only thing is that you need to use a derived table rather than using it directly in update. |
|
|
|
irey
Starting Member
3 Posts |
Posted - 2008-11-24 : 05:56:45
|
Ok, all of this solutions work perfectly. I really didn't know that "select into" could be used to create a table, nor that the "identity" property would update old values.Thanks  |
|
|
|
irey
Starting Member
3 Posts |
Posted - 2008-11-24 : 06:05:39
|
| I said a stupid thing, identity won't actually update old values, but it can create a new column setting a value even for old rows. Then I can drop the old column and rename the new one to the old name. It will be faster than creating a new table :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-24 : 06:15:58
|
quote:
Originally posted by irey
I said a stupid thing, identity won't actually update old values, but it can create a new column setting a value even for old rows. Then I can drop the old column and rename the new one to the old name. It will be faster than creating a new table :)
Yup. that what i told. refer below link also as before rememeber to script out constraints of old column before dropping if any and apply it to new one afterwardshttp://www.mssqltips.com/tip.asp?tip=1397 |
|
|
|
|
adding an ID to an already populated table