SQL 更改表添加列语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/794371/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter Table Add Column Syntax
提问by BuddyJoe
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.
我究竟做错了什么？我试图导出脚本，但 SQL Mgmt Studio 做了整个临时表重命名的事情。
UPDATE: I think it is choking on the first statement with "Incorrect syntax near the keyword 'COLUMN'."
更新：我认为第一个语句“关键字'COLUMN'附近的语法不正确”令人窒息。
回答by Vikram
Just remove COLUMNfrom ADD 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]
回答by Chirag Thakar
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
回答by Dzianis Yafimau
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);
COLUMNkeyword in SQL SERVER is used only for altering:
COLUMNSQL SERVER 中的关键字仅用于更改：
ALTER TABLE table_name
ALTER COLUMN column_name column_type;
回答by neouser99
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.
如果您尝试在表的开头添加一列，则可能会进行临时表重命名（因为这比更改顺序更容易）。另外，如果Employees表中有数据，则必须执行insert select *才能计算EmployeeID。
