vb.net 将自动递增主键添加到 SQL SERVER 2005 中的现有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19263019/
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
Adding auto incrementing primary key to an existing table in SQL SERVER 2005
提问by user2541172
I have a table with 8,000 rows of data and will be adding more. but I forgot to put a primary key in the beginning. so that each row has a unique key. later i added a primary key column. but that column is NULL now.
我有一个包含 8,000 行数据的表，并将添加更多数据。但我忘了在开头放一个主键。这样每一行都有一个唯一的键。后来我添加了一个主键列。但该列现在为 NULL。
I want the first row to start at ID 1 and increment all the way up to the last row at ID 8000. How do I update all of the rows with a single query?
我希望第一行从 ID 1 开始并一直递增到 ID 8000 处的最后一行。如何使用单个查询更新所有行？
i am using SQL Server 2005.
我正在使用 SQL Server 2005。
采纳答案by Shahid Iqbal
Open Table Design, Add New Column u want Select the column and in PropertiesIn Identity Specificationmake (Is Identity)Yes.. You can start from where you want by setting the Identity Seedproperty, by Default it starts from 1.
打开表格 设计，添加您想要的新列选择列并在身份规范中的属性中制作（是身份）是的..您可以通过设置身份种子属性从您想要的位置开始，默认情况下它从1开始。
If you have already Identity Columnu can also update it.
Step 1: Remove Identity Specification from Column in Table Design.
Step 2: Use Cursor to Update table Column starting from 1.
Step 3: Again apply Identity Specification on Column in Table Design
Step 4: By query reset Identity Column, from the value u want.
e.gDBCC CHECKIDENT("TableName",Reseed,8000);
so the next identity value will be 8001.
如果您已经有身份列，您也可以更新它。
步骤 1：从表设计中的列中删除标识规范。
第 2 步：使用 Cursor 从 1 开始更新表列。
第 3 步：再次在表设计中对列应用标识规范
第 4 步：通过查询重置标识列，从您想要的值开始。例如DBCC CHECKIDENT("TableName",Reseed,8000);
所以下一个标识值将是 8001。
回答by Pradip
As per your requirement you just needed a sql query that will update the entire table with the new primary key values in an incremental fashion. Here is is :
根据您的要求，您只需要一个 sql 查询，它将以增量方式使用新的主键值更新整个表。这是：
UPDATE myTable
SET ID = ID + 1
Where ID is the PK field name
其中 ID 是 PK 字段名称
Once Updated do not forget to add identity column as shown below :
更新后不要忘记添加身份列，如下所示：
ALTER TABLE table
ADD ID INT IDENTITY
ALTER TABLE table
ADD CONSTRAINT PK_table
PRIMARY KEY(ID)
回答by Lloyd Banks
Get rid of the column you just added. Then run this
删除您刚刚添加的列。然后运行这个
ALTER TABLE table
ADD ID INT IDENTITY
ALTER TABLE table
ADD CONSTRAINT PK_table
PRIMARY KEY(ID)
回答by Appyks
CREATE TABLE [dbo].[TheTable](
[TheID] [int] IDENTITY(1,1) NOT NULL,
[TheColumn] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_TheTable] PRIMARY KEY CLUSTERED
(
[TheID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[TheTable]
( [TheColumn] )
SELECT 'one'
UNION
SELECT 'two'
SELECT * FROM [dbo].[TheTable] TT
回答by Amir Keshavarz
You can update table based on unique key. Declare @i int=1 While(@i <=(select count(*) from table)) Begin
您可以根据唯一键更新表。声明@i int=1 While(@i <=(select count(*) from table)) 开始
Update table Set primary_key_column=@i Where
更新表 Set primary_key_column=@i Where
Set@i=@i+1 End
设置@i=@i+1 结束
