SQL Server：使用添加新列的 ALTER
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9332262/
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
SQL Server: ALTER with ADD new column
提问by Nate Pet
I have the following statement in SQL Server:
我在 SQL Server 中有以下语句：
ALTER TABLE [dbo].[TruckTbl]
ADD [TruckState] [bit] NULL DEFAULT 0;
What this will do is to default any NEW record to 0.
这将做的是将任何新记录默认为 0。
For existing records how can I get and say in the statement also default those to 0.
对于现有记录，我如何在语句中获取和说也将这些记录默认为 0。
I know I can go and do an update but anyway to do within an update in the ALTERstatement above?
我知道我可以进行更新，但无论如何要在上述ALTER声明中的更新中进行？
回答by Martin Smith
Use the WITH VALUESclause
使用WITH VALUES条款
ALTER TABLE [dbo].[TruckTbl] ADD [TruckState] [bit] NULL DEFAULT 0 WITH VALUES;
Although I do agree with the other answer it seems odd that the column should be nullable at all if you are setting all existing rows to 0and have a default for future inserts. Do you ever need to allow NULLas a column value here?
尽管我同意另一个答案，但如果您将所有现有行设置为0并且为将来的插入设置默认值，则该列应该可以为空似乎很奇怪。您是否需要在NULL此处允许作为列值？
回答by Chirag Thakar
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
E.g
例如
ALTER TABLE Temp
ADD ID int NOT NULL DEFAULT(1)
