在 SQL Server 中将现有主列更改为自动递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22550893/
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
Alter Existing Primary Column to Auto Increment in SQL Server
提问by nigel
I have a table and it already have a primary key, the data type is integer. The problem is the column is not set to auto increment once a row is being inserted. I have to check the max then add 1 to manually auto increment to column. Is there a way to alter the column to set the primary key to auto increment? I'm going to add hundreds of records and adding the id manually is quite a tedious task. I cannot drop the column since there are IDs that are already existing. Thanks in advance.
我有一个表，它已经有一个主键，数据类型是integer. 问题是一旦插入一行，列就没有设置为自动递增。我必须检查最大值然后添加 1 以手动自动增加到列。有没有办法改变列以将主键设置为自动递增？我要添加数百条记录，手动添加 id 是一项相当乏味的任务。我无法删除该列，因为已经存在 ID。提前致谢。
回答by cmsjr
You can't change the definition of an existing column to be an identity. The closest you can come, while retaining any existing ids, is to;
您不能将现有列的定义更改为标识。在保留任何现有 ID 的同时，您可以到达的最接近的是；
- Create a new table with the same definition, but with the identity
- Configure the new table to allow inserts of specific values (using
Set Identity_Insert On) - Select data from the existing table into the new table
- Turn Identity_Insert back off
- Drop or rename the existing table
- Rename the new table to the original table's name.
- 创建一个具有相同定义但具有标识的新表
- 配置新表以允许插入特定值（使用
Set Identity_Insert On） - 从现有表中选择数据到新表中
- 重新关闭 Identity_Insert
- 删除或重命名现有表
- 将新表重命名为原始表的名称。
Something like this
像这样的东西
Create Table Table1 (id int, Name varchar(20))
Insert Into Table1 values (1,'Name 1')
Insert Into Table1 values (2,'Name 2')
Create Table Table2 (Id int identity, Name varchar(20))
Go
Set Identity_Insert Table2 on
Go
Insert into Table2 (Id, Name) Select Id,Name from Table1
Set Identity_Insert Table2 Off
Go
Exec sp_Rename Table1, TableOld, 'Object'
Exec sp_Rename Table2, Table1, 'Object'
Insert into Table1 (Name) values ('Name 3')
回答by Vipin Mittal
Use This :
用这个 ：
