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

score 206 · Accepted Answer · edited Apr 27 '09 at 17:02

206

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]

edited Apr 27 '09 at 17:02

Eoin Campbell

42,194
17
98
153

answered Apr 27 '09 at 17:02

Vikram

6,807
8
49
59

11

To clarify, the 'COLUMN' keyword is only valid (but not required) in MySQL. – ethanbustad Feb 12 '15 at 21:28
5

@ethanbustad It is also valid for DB2 and Postgres. – ᴠɪɴᴄᴇɴᴛ Sep 02 '16 at 14:39
3

Ridiculous that they don't just ignore the `column` keyword and make it work like every other DB out there. – Bruce Pierson Apr 12 '18 at 19:36
FYI, Oracle behaves in the same way as SQL Server in this case - both don't allow `COLUMN` in the `ADD` statement. – bsplosion Jun 24 '19 at 17:47

score 16 · Answer 2 · answered Apr 10 '17 at 11:15

16

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

answered Apr 10 '17 at 11:15

Chiragkumar Thakar

3,426
5
35
48

score 8 · Answer 3 · answered Jan 27 '16 at 08:58

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;

Thanks for mentioning multiple columns. – PAS Dec 09 '20 at 12:57 — PAS, Dec 09 '20 at 12:57

score 0 · Answer 4 · answered Apr 27 '09 at 17:03

0

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.

answered Apr 27 '09 at 17:03

neouser99

1,767
1
9
22

1

"easier that altering the order" - Do you mean that it is possible (although it is more difficult) to alter the order of the columns without recreating the table (through a temp table)? – Örjan Jämte Sep 16 '09 at 11:41
1

In a relational database, you should never have a need for the ordinality of the columns so if you are trying to neatly order the columns, the question is why? If column ordinality was so important, why isn't there a trivial function to swap or fix ordinality of columns? The reason is it designed for ordinality to not matter. – Shiv Nov 12 '14 at 03:54

Alter Table Add Column Syntax

4 Answers4

Linked