如何复制另一表IDENTITY列并为缺失记录生成新ID，解决标识列更新错误

报错根因 #

• SET IDENTITY_INSERT 仅对INSERT操作生效，不支持通过UPDATE修改标识列的已有值
• 原代码中的UPDATE关联条件错误，两表是通过Code字段关联，而非还未赋值的ID字段

分步实现方案 #

步骤1：给TableA新增普通INT类型的ID列 #

先不设置为标识列，避免后续无法手动赋值：

ALTER TABLE [dbo].[TableA] ADD [ID] INT NULL;

步骤2：同步TableB的ID到TableA的匹配记录 #

UPDATE a
SET a.ID = b.Id
FROM TableA a
INNER JOIN TableB b ON a.Code = b.Code;

步骤3：为TableB无匹配的记录生成唯一ID #

保证生成的ID最小值为20000，和TableB已有ID范围不冲突：

DECLARE @StartId INT = 19999;
UPDATE TableA
SET @StartId = ID = @StartId + 1
WHERE ID IS NULL;

步骤4：将ID列设置为标识列，支持后续自增 #

SQL Server不支持直接将普通列修改为IDENTITY属性，可根据场景选择以下两种方式：

小表场景（数据量低于10万行）

使用SSMS界面修改即可：

1. 打开SSMS顶部菜单【工具】→【选项】→【设计器】，取消勾选【阻止保存要求重新创建表的更改】
2. 打开TableA的表设计界面，找到ID列，将「标识规范」设置为「是」，标识种子填当前TableA的最大ID+1，标识增量填1，保存即可。

大表生产场景

用纯SQL做表切换，最小化锁表时间：

-- 1. 新建结构和TableA完全一致的新表，ID设为标识列
CREATE TABLE dbo.TableA_New (
    Code VARCHAR(10) PRIMARY KEY, -- 和原TableA的Code字段属性保持一致
    ID INT IDENTITY(20004,1), -- 示例值，替换为你当前TableA最大ID+1
    -- 请补齐TableA其他所有原有字段，属性和原表完全一致
)

-- 2. 导入原有数据到新表
SET IDENTITY_INSERT dbo.TableA_New ON;
INSERT INTO dbo.TableA_New (Code, ID /* 此处补齐其他所有字段 */)
SELECT Code, ID /* 此处补齐其他所有字段 */ FROM dbo.TableA;
SET IDENTITY_INSERT dbo.TableA_New OFF;

-- 3. 原子替换原表
BEGIN TRANSACTION
EXEC sp_rename 'dbo.TableA', 'TableA_Old';
EXEC sp_rename 'dbo.TableA_New', 'TableA';
COMMIT TRANSACTION

-- 验证数据无误后可删除旧备份表：DROP TABLE dbo.TableA_Old

内容的提问来源于stack exchange，提问作者Liero