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

User · Answer

It could be doing the temp table renaming if you are trying to add a column to the beginning of the table  as this is easier than altering the order   Also  if there is data in the Employees table  it has to do insert select   so it can calculate the EmployeeID

User · Answer

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

User · Answer

This is how Adding new column to Table  ALTER TABLE  tableName  ADD ColumnName Datatype   E g  ALTER TABLE  Emp  ADD Sr No Int   And If you want to make it auto incremented  ALTER TABLE  Emp  ADD Sr No Int IDENTITY 1 1  NOT NULL

User · Answer

The correct syntax for adding column into table is   ALTER TABLE table name   ADD column name column-definition    In your case it will be   ALTER TABLE Employees   ADD EmployeeID int NOT NULL IDENTITY  1  1    To add multiple columns use brackets   ALTER TABLE table name   ADD  column 1 column-definition         column 2 column-definition                    column n column definition     COLUMN keyword in SQL SERVER is used only for altering   ALTER TABLE table name   ALTER COLUMN column name column type

[sql] Alter Table Add Column Syntax

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to ddl