I have a load of tables with an ID column that needs to be updated to an identity and primary key. Reading around, the following suggestion doesn't seem to work. Could someone advise...

ALTER TABLE <table>
ALTER COLUMN <column__name> INT IDENTITY NOT NULL
CONSTRAINT PRIMARY KEY

I am getting

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IDENTITY'.

I should add that the tables contain data, including the ID columns.

Regards,

Sean Anderson

__________________
Regards,

Sean Anderson

Try by providing Identity seed and increment.
ALTER TABLE <table>
ALTER COLUMN <column__name> INT IDENTITY(1,1) NOT NULL
CONSTRAINT PRIMARY KEY

Bijgupt

You can't add the IDENTITY property to an existing column... you will need to add a new column with the identity property.

--Jeff Moden