Alter Table Add Column Syntax

Question

I'm trying to programmatically add an identity column to a table Employees. Not sure what I'm doing wrong with my syntax.

ALTER TABLE Employees

  ADD COLUMN EmployeeID int NOT NULL IDENTITY (1, 1)

ALTER TABLE Employees ADD CONSTRAINT

    PK_Employees PRIMARY KEY CLUSTERED 

    (

      EmployeeID

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

What am I doing wrong? I tried to export the script, but SQL Mgmt Studio does a whole Temp Table rename thing.

UPDATE: I think it is choking on the first statement with "Incorrect syntax near the keyword 'COLUMN'."

Answer 1

Try to remove COLUMN from ADD COLUMN.

Use below query:

ALTER TABLE Employees

  ADD EmployeeID numeric NOT NULL IDENTITY (1, 1)

ALTER TABLE Employees ADD CONSTRAINT

        PK_Employees PRIMARY KEY CLUSTERED 

        (

          EmployeeID

        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]