SQL 向现有表添加新列，其值等于 ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4131451/
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 new column to an existing table, with a value equal to the ID
提问by Edwin de Koning
In my database in a sql-server 2005 instance, I need to add a new column to a table with existing data. The table currently looks like this:
在我的 sql-server 2005 实例中的数据库中，我需要向包含现有数据的表中添加一个新列。该表目前如下所示：
CREATE TABLE [dbo].[Status](
[Status_ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [nvarchar](80) COLLATE Latin1_General_CI_AS NOT NULL
)
The column I want to add is also of type INT and nulls are not allowed. Furthermore, I want the initialvalues for this column to be equal to the ID (it cannot be a computed column).
我要添加的列也是 INT 类型，不允许为空值。此外，我希望此列的初始值等于 ID（它不能是计算列）。
This is the script I have written for this:
这是我为此编写的脚本：
ALTER TABLE [dbo].[Status]
add Status_Code int NOT NULL DEFAULT 1
GO
//Get the number of rows
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(Status_ID) FROM Status)
//Create an iterator
DECLARE @I INT
SET @I = 1
//Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
UPDATE Status
SET Status_Code = @I
WHERE Status_ID = @I
//Increment the iterator
SET @I = @I + 1
END
This script seems to work perfectly. However, it seems like a lot of code for a rather small task. Does anyone know of a more efficient way to code this?
这个脚本似乎工作得很好。然而，对于一个相当小的任务来说，它似乎有很多代码。有谁知道更有效的编码方式？
回答by cjk
Why loop through the table to do the update? Just do this:
为什么要遍历表来进行更新？只需这样做：
ALTER TABLE [dbo].[Status]
add Status_Code int NOT NULL DEFAULT 1
UPDATE Status
SET Status_Code = Status_ID
回答by Gerrie Schenck
Create the new column, and make it nullable.
创建新列，并使其可为空。
Then write a simple update statement to insert the existing id's into the new columns.
然后编写一个简单的更新语句将现有的 id 插入到新列中。
Then make your column not-nullable.
然后使您的列不可为空。
回答by Sadhir
I would go with Gerrie's answer as you want the column to be NOT NULL. If you specify a default value, SQL server will still allow you to leave out that column in any subsequent insert statement (the column will be assigned the default value)
我会同意 Gerrie 的答案，因为您希望该列不是 NULL。如果您指定默认值，SQL Server 仍将允许您在任何后续插入语句中省略该列（该列将被分配默认值）
