Update a Current table Column to an Identity Column [duplicate]

Question

Possible Duplicate:
set identity on the column

I have a table let's say Messages with a column name Serno. Serno keeps the serial number for each message. However system is very old and creates this serial number using max(serno) command. Now, I am trying to change this column to an identity but I don't want to loose the current serial numbers for each message.

Is there a solution for this.

I already try to creatae a new identity column and copy the values from serno there but it doesn't work. Neither creating a new table work as database will give new serial numbers for each message and I don't want that.

Any ideas are Welcome.

Thanks in advance

M.A

Answer 1

have you tried opening the table in design view, selecting your column, and setting the IdentitySpecification to Yes (this may take a while if you have many rows, as it internally generates a complete copy of the table)

Answer 2

You cannot alter the existing column and make it as an identity column.

You need to create a temporary table and insert the values to that. Finally you have to drop the actual table and rename the temp table to actual table

CREATE TABLE dbo.NewTable(ID int IDENTITY(1, 1),<other columns>)

SET IDENTITY_INSERT dbo.NewTable ON

INSERT  INTO dbo.NewTable ( Id, <other columns>)
SELECT  Id,  <other columns> FROM    <actual_table>
go

SET IDENTITY_INSERT dbo.NewTable OFF
go

DROP TABLE your_table
go

Exec sp_rename 'NewTable', 'actual_table'