将现有列转换为标识符。

Question

我在SQL Server中有一张包含很多记录的表格。我想将主键列ID转换为自增列，但不希望丢失数据。我考虑了以下两种方法：

1. 新建一个带有自增列的表格，并删除原始表格。
2. 新建一个带有自增列的列，并删除原始列。

但显然它们都不能被实施，因为保留记录是我的首要任务。

是否还有其他方法可以做到这一点？

- Billz

---

可能是向现有列添加标识的重复问题。 - juergen d

重复但我也提到了限制 - Billz

你的ID列已经作为外键与其他表连接了吗？ - kinske

我看不出将记录保留在任何一种方法中会存在问题。也没有其他选择。你只需要创建一个新的表，如果你想保留已有的ID。 - Nenad Zivkovic

@NenadZivkovic，因为提问者标记了Sql Server 2012，所以有一个替代方案。steoleary的答案可以解决这个问题 - 我自己在这种情况下也使用了同样的解决方案。 - Tim S

Answer 1

这个解决方案违反了您提到的第二点，但没有其他方法，我觉得您的目标是保留旧值，因为别无他法...

您可以采取以下措施:

1. make it possible to insert into identity columns in your table:

   set identity_insert YourTable ON
   

2. add a new ID column to your table with identity and insert the values from your old columns

3. turn identity insert off

   set identity_insert YourTable OFF
   

4. delete old ID column
5. rename new column to old name
6. make it to the primary key

唯一的问题可能是您已将ID列连接为其他表的外键。然后，您在删除旧列时会遇到问题... 在这种情况下，在第3步之后必须删除ID列上的外键约束，然后执行第4至6步，最后重新创建外键约束。

- kinske

Answer 2

如果您正在使用SQL Server 2012，另一种可能的选择是创建一个序列对象，该对象具有表中最高ID +1的起始值，然后使用GET NEXT VALUE FOR为列创建默认约束，并引用您刚刚创建的序列对象。

- steoleary

---

我曾经遇到过完全相同的问题，并采取了以下方式解决。效果非常好。 - Tim S

当时我想包含一个例子：https://dev59.com/BWYq5IYBdhLWcg3whRDV - Tim S

如果你有一个GUID/唯一标识符，你会怎么做？ - Alexey Shevelyov

Answer 3

如果您可以直接访问服务器数据库，只需进入表格的设计模式，选择PK列，将identity更改为“Yes”。确保将种子设置为该列的最大值。默认情况下，增量为1。保存表格设计，然后您就可以开始使用了。

- Red_Phoenix

Answer 4

考虑源表不太大的情况：

1. 创建新表（带有IDENTITY）
2. 从现有表中填充新表（打开IDENTITY_INSERT）
3. 删除旧表（先删除任何现有的FK）

4. 将新表重命名为旧名称（如果需要，重新建立FK）

      -- 创建示例现有表
      DROP TABLE IF EXISTS #tblTest
      CREATE TABLE #tblTest
      (
           ID      INT NOT NULL
           , Val   VARCHAR(10) NOT NULL
       )
   
       INSERT INTO #tblTest 
       (
           ID
           , Val
       )
       VALUES 
           (1, 'a')
           , (2, 'b')
           , (4, 'c')
       GO
   
       -- 创建并填充新表（打开IDENTITY_INSERT）
       DROP TABLE IF EXISTS #tblTestNew
       CREATE TABLE #tblTestNew
       (
           ID      INT IDENTITY(1, 1) NOT NULL 
           , Val   VARCHAR(10) NOT NULL
       )
   
       SET IDENTITY_INSERT #tblTestNew ON
   
       INSERT INTO #tblTestNew 
       (
           ID
           , Val
       )
       (
           SELECT
               #tblTest.ID
               , #tblTest.Val
           FROM
               #tblTest
       )
   
       SET IDENTITY_INSERT #tblTestNew OFF
       GO
   
       -- 将现有表重命名为旧表（可以使用sp_rename，但我不能用于临时表）
       SELECT * INTO #tblTestOld FROM #tblTest
       DROP TABLE #tblTest
       GO
   
       -- 将新表重命名为现有表（可以使用sp_rename，但我不能用于临时表）
       SELECT * INTO #tblTest FROM #tblTestNew
       DROP TABLE #tblTestNew
       GO
   
       -- 测试插入新记录
       INSERT INTO #tblTest (Val)
       VALUES ('d')
   
       -- 验证结果
       SELECT * FROM #tblTest
       EXEC tempdb.sys.sp_help @objname = N'#tblTest'
   
       -- 删除“旧”表（准备好后）
       DROP TABLE IF EXISTS #tblTestOld
   
       -- 清理
       DROP TABLE IF EXISTS #tblTest
       DROP TABLE IF EXISTS #tblTestNew
       DROP TABLE IF EXISTS #tblTestOld
   

如果表格非常大，请考虑日志增长、恢复模型、可能的单用户模式等。

- Mike Zach

Answer 5

create table t1 (col1 int, col2 varchar(10))

insert into t1 values (10, 'olddata')

--添加身份列

alter table t1 add col3 int identity(1,1)

GO

--重命名或删除旧列

alter table t1 drop column col1

--将新列重命名为旧列名称

exec sp_rename 't1.col3', 'col1', 'column'

GO

--添加新的测试，审核表格

insert into t1 values ( 'newdata')

选择 * 从 t1

- vhadalgi