向现有表中添加一列并在 MS SQL Server 上对其进行唯一编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/108211/
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 existing table and uniquely number them on MS SQL Server
提问by Adhip Gupta
I want to add a columnto an existing legacy databaseand write a procedureby which I can assign each record a different value. Something like adding a columnand autogenerate the data for it.
我想将一列添加到现有的旧数据库并编写一个过程，通过该过程我可以为每条记录分配不同的值。类似于添加一列并为其自动生成数据。
Like, if I add a new columncalled "ID" (number) I want to then initialize a unique value to each of the records. So, my ID columnwill have records from say 1 to 1000.
How do I do that?
就像，如果我添加一个名为“ID”（数字）的新列，我想为每个记录初始化一个唯一值。所以，我的 ID列将有来自 say 的记录1 to 1000。
我怎么做？
回答by Simon Johnson
This will depend on the database but for SQL Server, this could be achieved as follows:
这将取决于数据库，但对于 SQL Server，这可以按如下方式实现：
alter table Example
add NewColumn int identity(1,1)
回答by Tom Martin
It would help if you posted what SQL database you're using. For MySQL you probably want auto_increment:
如果您发布了您正在使用的 SQL 数据库，这会有所帮助。对于 MySQL，您可能需要 auto_increment：
ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO_INCREMENT KEYNot sure if this applies the values retroactively though. If it doesn't you should just be able to iterate over your values with a stored procedure or in a simple program (as long as no one else is writing to the database) and set use the LAST_INSERT_ID()function to generate the id value.
不确定这是否可以追溯地应用这些值。如果不是，您应该能够使用存储过程或在简单程序中迭代您的值（只要没有其他人写入数据库）并设置使用该LAST_INSERT_ID()函数来生成 id 值。
回答by Roy Tang
for oracle you could do something like below
对于 oracle，您可以执行以下操作
alter table mytable add (myfield integer);
update mytable set myfield = rownum;
回答by Flavien Volken
And the Postgres equivalent (second line is mandatory only if you want "id" to be a key):
和 Postgres 等价的（第二行是强制性的，只有当你想要“id”作为一个键时）：
ALTER TABLE tableName ADD id SERIAL;
ALTER TABLE tableName ADD PRIMARY KEY (id);
回答by Ilya Kochetov
Just using an ALTER TABLE should work. Add the column with the proper type and an IDENTITY flag and it should do the trick
只使用 ALTER TABLE 应该可以工作。添加具有正确类型和 IDENTITY 标志的列，它应该可以解决问题
Check out this MSDN article http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspxon the ALTER TABLE syntax
查看这篇 MSDN 文章http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx关于 ALTER TABLE 语法
回答by Snziv Gupta
for UNIQUEIDENTIFIER datatype in sql server try this
对于 sql server 中的 UNIQUEIDENTIFIER 数据类型试试这个
Alter table table_name
add ID UNIQUEIDENTIFIER not null unique default(newid())
If you want to create primary key out of that column use this
如果要从该列中创建主键，请使用此
ALTER TABLE table_name
ADD CONSTRAINT PK_name PRIMARY KEY (ID);
回答by Jinlye
If you don't want your new column to be of type IDENTITY(auto-increment), or you want to be specific about the order in which your rows are numbered, you can add a column of type INT NULLand then populate it like this. In my example, the new column is called MyNewColumn and the existing primary key column for the table is called MyPrimaryKey.
如果您不希望新列是类型IDENTITY（自动增量），或者您想要具体说明行编号的顺序，您可以添加一个类型的列，INT NULL然后像这样填充它。在我的示例中，新列称为 MyNewColumn，表的现有主键列称为 MyPrimaryKey。
UPDATE MyTable
SET MyTable.MyNewColumn = AutoTable.AutoNum
FROM
(
SELECT MyPrimaryKey,
ROW_NUMBER() OVER (ORDER BY SomeColumn, SomeOtherColumn) AS AutoNum
FROM MyTable
) AutoTable
WHERE MyTable.MyPrimaryKey = AutoTable.MyPrimaryKey
This works in SQL Sever 2005 and later, i.e. versions that support ROW_NUMBER()
这适用于 SQL Sever 2005 及更高版本，即支持 ROW_NUMBER()
回答by Joshua
Depends on the database as each database has a different way to add sequence numbers. I would alter the table to add the column then write a db script in groovy/python/etc to read in the data and update the id with a sequence. Once the data has been set, I would add a sequence to the table that starts after the top number. Once the data has been set, set the primary keys correctly.
取决于数据库，因为每个数据库都有不同的添加序列号的方式。我会更改表以添加列，然后在 groovy/python/etc 中编写一个 db 脚本以读取数据并使用序列更新 id。设置好数据后，我将向表中添加一个序列，该序列在顶部数字之后开始。设置好数据后，正确设置主键。
