Change Int to Identity column - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Change Int to Identity column

Author

Topic

Vijay
Starting Member

8 Posts

Posted - 2001-12-13 : 16:34:54

Hi,
I am trying to change the int column (not null) to identity column.
If I use the following script
"ALTER TABLE Names
alter column USER_ID int IDENTITY
"
I got this error' Incorrect syntax near the keyword 'IDENTITY'.
I can change it using enterprise manager, butI cannot run the script. I will greatly appreciate any suggestions.

Thanks in advance.

Vijay

ToddV
Posting Yak Master

218 Posts

Posted - 2001-12-13 : 17:28:36

Your enterprise manager goes through the following steps to make this change.
1. Create new table with desired definiton.
2. Insert data into new table.
3. Delete Old table
4. Rename old table to new table

You can script it out like that or , and this is easier:
Open the design view in EM.
Make the changes using the GUI with out saving.
Click the save transcript icon (third from the left).

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-13 : 18:05:00

quote:
4. Rename old table to new table

Sometimes typing gets in the way. Todd, isn't that supposed to be "Rename new table to old table"?

Vijay, I encourage you to take Todd's second suggestion and let EM build the script for you because if you have Foreign Keys and other constraints and indexes on the table you are modifying, all of those have to be dropped and re-created in your script too. EM should include all these automatically, thus saving you a lot of effort writing.