SQL Server:如何添加新的标识列和填充列与ids? [英] SQL Server: how to add new identity column and populate column with ids?
问题描述
我有一个有大量数据的表。我想添加额外的列 id
并将其用作主键。使用从 1
到行计数
I have a table with huge amount of data. I'd like to add extra column id
and use it as a primary key. What is the better way to fill this column with values from one 1
to row count
目前我正在使用游标和逐行更新行。需要几个小时。有更快的方法吗?
Currently I'm using cursor and updating rows one by one. It takes hours. Is there a way to do that quicker?
谢谢
推荐答案
只要这样做:
ALTER TABLE dbo.YourTable
ADD ID INT IDENTITY(1,1)
,将创建该列并自动使用 integer
值(正如Aaron Bertrand在他的评论中指出的 - 你不能控制哪一行获得什么值 - SQL Server自己处理它,你不能影响它,但是所有行都会得到一个有效的 int
value - 不会有任何 NULL
或重复的值)。
and the column will be created and automatically populated with the integer
values (as Aaron Bertrand points out in his comment - you don't have any control over which row gets what value - SQL Server handles that on its own and you cannot influence it. But all rows will get a valid int
value - there won't be any NULL
or duplicate values).
接下来,设置主键:
ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable PRIMARY KEY(ID)
这篇关于SQL Server:如何添加新的标识列和填充列与ids?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!