Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

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

1 Answer

0 votes
by (40.4k points)

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]

Related questions

0 votes
1 answer
asked Jul 26, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Jul 25, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...