SQL 向表中添加一列，其默认值等于现有列的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13249936/
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
Add a column to a table with a default value equal to the value of an existing column
提问by doesdos
How to add a column to a SQL Server table with a default value that is equal to value of an existing column?
如何将列添加到 SQL Server 表中，默认值等于现有列的值？
I tried this T-SQL statement:
我试过这个 T-SQL 语句：
ALTER TABLE tablename
ADD newcolumn type NOT NULL DEFAULT (oldcolumn)
but it's giving an error:
但它给出了一个错误：
The name "oldcolumn" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
在此上下文中不允许使用名称“oldcolumn”。有效的表达式是常量、常量表达式和（在某些上下文中）变量。不允许使用列名。
采纳答案by Kapil Khandelwal
Try this:
尝试这个：
ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (0)
Go
Update tablename SET newcolumn = oldcolumn Where newcolumn = 0
Go
回答by ypercube??
I don't like them very much but here is how you could do this with an AFTER INSERTtrigger:
我不太喜欢它们，但这里是你如何用AFTER INSERT触发器做到这一点：
CREATE TRIGGER TableX_AfterInsert_TRG
ON TableX
AFTER INSERT
AS
UPDATE TableX AS t
SET t.newcolumn = t.oldcolumn
FROM Inserted AS i
WHERE t.PK = i.PK ; -- where PK is the PRIMARY KEY of the table
回答by Herman Kan
The AFTER INSERTtrigger approach involves overhead due to the extra UPDATEstatement. I suggest using an INSTEAD OF INSERTtrigger, as follows:
AFTER INSERT由于额外的UPDATE语句，触发器方法涉及开销。我建议使用INSTEAD OF INSERT触发器，如下所示：
CREATE TRIGGER tablename_on_insert ON tablename
INSTEAD OF INSERT
AS
INSERT INTO tablename (oldcolumn, newcolumn)
SELECT oldcolumn, ISNULL(newcolumn, oldcolumn)
FROM inserted
This does not work though if the oldcolumnis an auto-identity column.
如果oldcolumn是自动标识列，则这不起作用。
回答by Frédéric Chauvière
To extend Kapil's answer, and avoid the unwanted default constraint, try this:
要扩展Kapil的答案，并避免不需要的默认约束，请尝试以下操作：
ALTER TABLE tablename ADD newcolumn type NOT NULL CONSTRAINT DF_TMP_TABLENAME_NEWCOLUMN DEFAULT -9999
Go
Update tablename SET newcolumn = oldcolumn
Go
ALTER TABLE tablename DROP CONSTRAINT DF_TMP_TABLENAME_NEWCOLUMN
Go
Replace -9999 by 'noData' if your type is varchar, nvarchar, datetime,... or by any compatible data for other types: specific value doesn't matter, it will be wiped by the 2nd instruction.
如果您的类型是 varchar、nvarchar、datetime 或其他类型的任何兼容数据，请将 -9999 替换为 'noData'：特定值无关紧要，它将被第二条指令擦除。
回答by Vijai
You can use computed column to insert new column in a table based on an existing column value
您可以使用计算列根据现有列值在表中插入新列
ALTER TABLE dbo.TableName ADD NewColumn AS (OldColumn) PERSISTED;
OR, if you want to make some changes to the value based on existing column value, use
或者，如果您想根据现有列值对值进行一些更改，请使用
ALTER TABLE dbo.TableName ADD NewColumn AS (OldColumn * 1.5) PERSISTED;
回答by Tr?n Thanh Phong
For my case, I want to add a new not null unique column named CODE but I don't know about the value at the creation time. I set the default value for it by get a default value from NewID() then update later.
就我而言，我想添加一个名为 CODE 的新非空唯一列，但我不知道创建时的值。我通过从 NewID() 获取默认值来设置它的默认值，然后再更新。
ALTER TABLE [WIDGET] ADD [CODE] CHAR(5) NOT NULL DEFAULT(SUBSTRING(CONVERT(CHAR(36), NEWID()), 1, 5))
ALTER TABLE [dbo].[WIDGET] WITH CHECK ADD CONSTRAINT [UQ_WIDGET_CODE] UNIQUE ([CODE])
回答by user7040891
I think it will work if you use Set Identity_Insert <TableName> OFFand after the insert Statement that you wrote just use Set Identity_Insert <TableName> ON.
我认为如果您使用Set Identity_Insert <TableName> OFF并且在您编写的插入语句之后使用它会起作用Set Identity_Insert <TableName> ON。
