SQL Server跨库复制同结构表数据开启IDENTITY_INSERT仍报错如何解决？

核心问题梳理 #

• 报错指向的表是db1.dbo.MsSubProject，并非你正在操作的Subscriber表：绝大多数情况是Subscriber表上配置了INSERT触发器，触发后会自动写入MsSubProject表，该表也存在标识列，且你没有开启它的IDENTITY_INSERT配置，报错实际来源于触发器的执行逻辑，而非你直接写的INSERT语句。
• 第二段SQL存在表名不匹配错误：你开启IDENTITY_INSERT的表是[db1].[dbo].Subscriber，最后关闭时写的是[db1].[dbo].MsSubscriber，两个表名不一致，会导致IDENTITY_INSERT状态异常残留，后续操作也会受影响。
• SELECT语句使用*存在列映射风险：即使你确认两张表结构完全一致，*返回的列顺序如果和INSERT指定的列顺序不匹配，就会出现非标识列值写入标识列的情况，直接触发该报错。

修复步骤 #

1. 先检查db1.dbo.Subscriber表是否存在INSERT触发器，如果有，两种处理方式：要么临时禁用触发器，数据导入完成后再重新启用；要么同步给MsSubProject表也开启IDENTITY_INSERT配置，操作完成后关闭。
2. 修正IDENTITY_INSERT的开闭表名，必须保证开启和关闭的是同一个表，完整的正确SQL参考如下：

-- 操作主表前开启IDENTITY_INSERT
SET IDENTITY_INSERT [db1].[dbo].Subscriber ON

-- 如果有触发器需要操作MsSubProject，同步开启
-- SET IDENTITY_INSERT [db1].[dbo].MsSubProject ON

INSERT INTO [db1].[dbo].Subscriber (
    SubscriberGUID, ItemGUID_Entity, SubscriberID, 
    SubscriberRegionID, SubscriberTypeID, ID, 
    SubscriberNameFull, SubscriberEmail, SubscriberLogin, 
    SubscriberPassword, Active, DateCreated, DateDeleted
)
-- SELECT显式列名，和INSERT的列顺序完全对应，不要用*
SELECT 
    SubscriberGUID, ItemGUID_Entity, SubscriberID, 
    SubscriberRegionID, SubscriberTypeID, ID, 
    SubscriberNameFull, SubscriberEmail, SubscriberLogin, 
    SubscriberPassword, Active, DateCreated, DateDeleted
FROM [db2].[dbo].SubScriber

PRINT 'Successfully Re-imported data from SubScriber backup'

-- 关闭对应表的IDENTITY_INSERT
-- 如果开了MsSubProject的也要关
-- SET IDENTITY_INSERT [db1].[dbo].MsSubProject OFF
SET IDENTITY_INSERT [db1].[dbo].Subscriber OFF

3. 以上操作完成后如果还报错，再核对两个库中Subscriber表的标识列是否为同一个字段，标识列的属性是否完全一致，排除表结构实际不一致的问题。

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