sql - 向现有列添加身份(Adding an identity to an existing column)

Question

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

(我需要将表的主键更改为标识列，并且表中已经有许多行。)

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

(我有一个脚本来清理ID，以确保它们从1开始是连续的，在我的测试数据库上可以正常工作。)

What's the SQL command to alter the column to have an identity property?

(将列更改为具有标识属性的SQL命令是什么？)

  ask by Kirschstein translate from so

Answer 1

You can't alter the existing columns for identity.

(您不能更改现有的标识列。)

You have 2 options,

(您有2种选择，)

1. Create a new table with identity & drop the existing table

   (使用标识创建一个新表并删除现有表)

2. Create a new column with identity & drop the existing column

   (创建一个具有标识的新列并删除现有列)

Approach 1. ( New table ) Here you can retain the existing data values on the newly created identity column.

(方法1.（ 新表 ）在这里，您可以将现有数据值保留在新创建的标识列上。)

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Approach 2 ( New column ) You can't retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

(方法2（ 新列 ）您不能在新创建的标识列上保留现有数据值，标识列将保存数字序列。)

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

See the following Microsoft SQL Server Forum post for more details:

(有关更多详细信息，请参见以下Microsoft SQL Server论坛帖子：)

How to alter column to identity(1,1)

(如何将列更改为身份（1,1）)