Alter Table Add Column Syntax

sql sql-server tsql ddl
BuddyJoe picture BuddyJoe · Apr 27, 2009 · Viewed 211.1k times · Source

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

Vikram picture Vikram · Apr 27, 2009

Just remove COLUMN from ADD COLUMN

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

Invalid column name on sql server update after column create

Does anyone see what's wrong with this code for SQL Server? IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'OPT_LOCK' AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK')) BEGIN ALTER TABLE REP_DSGN_SEC_GRP_LNK …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More