Adding an Identity Later T-SQL

[vuyiswamb]

Good Day All

i have a Table with the ID Field that was not an identity Field. This Field has not Duplicates. Now later i want to Change this numeric Field as an Identity Field like this

--STEP 7 ADD THE IDENTITY BACK IN TABLE MTM_ACTV_STAFF
ALTER TABLE [New_Jaco].[dbo].[MTM_ACTV_STAFF]
ALTER COLUMN [ID] IDENTITY(1,1)

but i get an Error that says

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

[sknake]

You have to drop and re-create the table. You can't update identity columns with UPDATE . You need to use SET IDENTITY_UPDATE dbo.SomeTable ON . Then you can insert in the identity column

I would suggest using the SQL designer for this task

[vuyiswamb]

Thank you for your Help. This has Helped me.