Alter existing table to add a IDENTITY column

Hi this is my first visit to the MSSQL forum with a question.

Let me explain the scenario,

I have a table say clients table with the structure like id,foo,etc.. and lots of records on it. But the issue is this id column is not an IDENTITY column.
But the values for the Id column dont repeat since it has handled from the application level.Now I need to change this id column as an IDENTITY column with out loosing the records on the table.

If any of you can guide me over this problem, its highly appreciated.
Thanks.Hi
you have 2 choice.

1) Alter table change the column propertyes to identity column but i don't remember if this option keep old id value. you must try.
2) Copy all data of table in another table.
Change the column property Generate an insert statement from the copied dato to new table . Before you execute the insert statement you mast write SET Identity insert ON for the destioantion table.
When you finished execute Set Identity insert On

Hi|||Hi Ajaxrand,

If you have access to enterprise manager, you can browse to the table, right click => design.

In the definition of the table, select the column, set identity on and seed value to the last existing value in the table. Do save.

Next record inserted into to the table will have the next identity column auto incremented.

Obviously take a copy of the table fist and test before changing in the production environment :)

Regards Purple|||

Quote:

Originally Posted by

If you have access to enterprise manager, you can browse to the table, right click => design.

In the definition of the table, select the column, set identity on and seed value to the last existing value in the table. Do save.

I could reach to this step Mytable >> Design Table
But There is nothing called set identity on or seed the value foo bar.|||Hi ajaxrand,

highlight the column (actually a row in this presentation) you are interested in by left clicking the grey square to the left of the column name. With the row highlighted the column detail will be shown in the bottom half of the window with all the things you need.

Regards Purple|||

Quote:

Originally Posted by Purple

Hi ajaxrand,

highlight the column (actually a row in this presentation) you are interested in by left clicking the grey square to the left of the column name. With the row highlighted the column detail will be shown in the bottom half of the window with all the things you need.

Regards Purple

Gotcha, Thanks purple.

I have another Quiz. the Original table is coming from MsAccess and i converted it to MSSQL using import export utilty.
There were nearly 2000 records in the table.with the changes i made to the Table structure (Identity Column) will it change those values in the table.|||Hi Ajaxrand,

as long as you don't change the datatype the identity changes we discussed will not change the data in that column on the table.

I would always suggest to run it in a development environment and check it out first anyway..

Good luck !

Purple|||Hi,

Thanks Purple.
Thanks gpinetto

Regards,
-Ajaxrand