Alter an INT field to be Identity

[bschmitz]

I have an integer field with data that I am trying to change to the Identity field without losing any data. I have to do this using the osql utility. When I run the statement "Alter table table_name alter column column_name int identity", I get the following error "Incorrect syntax near the keyword identity".

I could have sworn that I had done this before as I had the script saved in my scripts folder. I know that the data is not duplicated in the field that I want to change. If anyone knows what I am doing wrong please advise me. Thanks in advance!!

 

[dodge20]

I think the syntax is as follows

alter table your_table modify column your_field identity

Dodge20

 

[bschmitz]

That gave me an incorrct syntax error also.

 

[dodge20]

I'm sorry, I forgot int infront of identity

alter table your_table modify column your_field int identity

Dodge20

 

[bschmitz]

I still get a syntax error. BOL uses the keyword alter.

 

[dodge20]

I was doing some reading, and you cannot alter the identity to a column. You can only add or drop. So if you want to do this, you will have to drop the column entirely, the re add it with the identity

Check out this page.

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20330357.html

Dodge20

 

[bschmitz]

Thanks for the help dodge20. I thought that might be the case but hoped I was wrong.

 

[tlbroadbent]

You cannot ALTER an existing column to convert it to an Identity. You'll have to do the following.

1) Create a new table with the Identity column defined.
2) Set identity_insert on for the new table.
3) Insert data from the old table into the new table.
4) Add indexes to the new table.
4) Drop the old table.
5) Rename the new table to the old name.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm