同库仅修改表名复制带Identity列的表并保留ID值的SQL报错排查

• 触发报错的直接原因：INSERT INTO My_TABLE_NEW 语句未显式声明新表的目标插入列清单。SQL Server对IDENTITY列的显式值插入有强制规则：即使手动开启了IDENTITY_INSERT开关，插入语句也必须明确列出所有要写入的列名，不允许通过SELECT语句的列顺序隐式匹配新表结构来插入自增列的值，这正是报错提示中“仅当指定列列表且开启IDENTITY_INSERT时，方可为表的Identity列显式指定值”的具体要求。
• 代码逻辑的隐性缺陷：
  • 判断表是否存在的查询没有增加用户表类型过滤（type = 'U'），如果数据库中存在同名的视图、存储过程、约束类对象，会出现判断逻辑误判，导致建表流程跳过或误执行插入。
  • 插入完成后没有显式校正新表的自增种子值，后续新插入数据时可能出现主键值冲突。

IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE name = 'My_TABLE_NEW' AND type = 'U'
)
BEGIN
    CREATE TABLE [dbo].[My_TABLE_NEW]
    (
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [OBJECT_ID] [int] NOT NULL,
        [YEAR_MONTH] [int] NOT NULL,
            
        CONSTRAINT [PK_My_TABLE_NEW] 
            PRIMARY KEY CLUSTERED ([ID] ASC)
                        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                              IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                              ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
END
GO

IF EXISTS (
    SELECT * FROM sys.objects 
    WHERE name = 'My_TABLE_NEW' AND type = 'U'
)
BEGIN
    SET IDENTITY_INSERT dbo.My_TABLE_NEW ON

    -- 核心修正：显式指定插入的目标列清单
    INSERT INTO dbo.My_TABLE_NEW (
        [ID],
        [OBJECT_ID],
        [YEAR_MONTH]
    )
    SELECT [ID],
           [OBJECT_ID],
           [YEAR_MONTH]
    FROM dbo.My_TABLE

    SET IDENTITY_INSERT dbo.My_TABLE_NEW OFF
    -- 校正自增种子值，避免后续插入出现主键冲突
    DBCC CHECKIDENT ('dbo.My_TABLE_NEW', RESEED)
END
GO

操作说明：

• IDENTITY_INSERT的开关仅在当前会话、指定表上生效，会话断开后设置自动失效，不会对其他连接造成影响
• 插入完成后执行DBCC CHECKIDENT RESEED会自动将自增起点调整为当前表中最大ID值的下一位，完全匹配保留原ID值的业务需求

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