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 |
|
rajadurai
Starting Member
3 Posts |
 Posted - 2005-02-24 : 14:57:12
|
| How do i Add Identity constraint to an Already existing Column?I have a table with one of the colummns as ID and it is of type numeric(18,0). I need it to be an Identity column so that it may increment on its own.Is there any way of doing it without droping and adding the column?Could some one help me?thanksraj |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-24 : 15:45:23
|
| Just make the change inside Enterprise Manager. Without saving it, click the save change script button. Take a look at the code that is required to do this sort of thing. It involves creating a temporary table with the schema that you want, then moving the data into that, then dropping your table, then renaming the temporary table to your table, and finally adding the extra stuff back in.Tara |
 |
|
|
rajadurai
Starting Member
3 Posts |
Posted - 2005-02-24 : 17:54:43
|
| Thanks tara, I couldn't locate the save change script button(I have Server 2000). But there was a Generate script which would drop the table and create a new one.However, i was wondering is there any command like alter column or add constraint that would let me add Identity to an existing column(make a column a identity column)?Thank youRaj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-24 : 18:00:48
|
| No the generate script utility would delete your data. Right click on your table, go to design table. Highlight the column, and put Yes in Identity section on the bottom of the screen. Click the save change script button. Notice the code. There isn't an alter option available for this type of change.Tara |
|
|
|
rajadurai
Starting Member
3 Posts |
Posted - 2005-02-24 : 18:18:43
|
| Tara,Thank you very much. thank you. I didn't notice the Save Change Script, thanks!Raj |
|
|
|
|
|
|
|
|
Identity column