Adding IDENTITY property to existing column

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)

Adding IDENTITY property to existing column

Author

Topic

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-11-13 : 08:38:15

Hi,

I have an integer column in a table without IDENTITY property. Now I want to add this property to that column without using Enterprise Manager. Using syntax oriented like:

ALTER TABLE <tab name> ALTER COLUMN <col> <datatype> IDENTITY(1,1)

I have tried like this, but sql serevr says error like "Incorrect syntax near the keyword 'IDENTITY'". Its an urgent requirement. send me your reply ASAP.

Note: That column data is unique.

Thanks,

":-) IT Knowledge is power :-)"

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-11-13 : 13:15:41

I would probably take the step of scripting a new table with IDENTITY and copy the rows into it. The process would be something like this:

Drop all FK's to your table.
Rename Table to Table_old
Create new table with IDENTITY field.
SET IDENTITY INSERT ON
INSERT Rows from Table_Old into Table putting your integer column into your new IDENTITY field.
SET IDENTITY INSERT OFF
Recreate All FKs and Indexes
DROP Table_Old

This is essentially the process that Enterprise Manager will take anyway.

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-13 : 13:26:11

SqlStar, just make the change in Enterprise Manager, but do not save it. Click on the save change script button to see the code. Then copy the code over to Query Analzyer. The code that EM uses is exactly the same code that would be required for Query Analyzer. The save change script button is very helpful to generate the code for you and be able to deploy it to multiple environments quickly.

Tara

Granick
Starting Member

46 Posts

Posted - 2003-11-13 : 13:37:07

Wow, you know, even after a couple+ years of working with SQL Server, I still learn something new just about every day. This will help out a great deal in much of what I do, especially since I tend to be lazy and put off writing alter tables and such till the last second otherwise.

Thanks for the great tip.

Shannon

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-11-14 : 00:01:50

Dear all,

Thanks for your reply.

":-) IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources