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'.

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

Thank you for your Help. This has Helped me.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.