Alter Table Add Column Syntax

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

---

只是删除COLUMN从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]

---

This is how Adding new column to Table

ALTER TABLE [tableName]
ADD ColumnName Datatype

Eg

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

---

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;

链接地址: http://www.djcxy.com/p/76314.html

上一篇: 使用默认值将Nullable列更改为NOT NULL

下一篇: 改变表格添加列语法