跨数据库复制数据遇PRIMARY KEY约束冲突，请求技术协助

问题背景 #

需要将数据复制到结构完全一致（库、表、列均匹配）的目标数据库，先后尝试两种脚本均在部分表触发主键约束冲突（错误码Msg 2627）：

1. 最初使用EXCEPT过滤重复行：

INSERT INTO [Db].[schema].[table] ([id],[name],[kod],[number])
SELECT [id],[name],[kod],[number]
FROM [MYLINKEDSRVR\Test].[Db].[schema].[table]
EXCEPT
SELECT [id],[name],[kod],[number]
FROM [Db].[schema].[table];

执行后部分表报错：

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_ui'. Cannot insert duplicate key in object 'schema.table'. The duplicate key value is (770132, Jul 1 2011 16:00AM).

2. 改用NOT EXISTS过滤后，仍有两张表报错：

INSERT INTO [Db].[schema].[table] ([id],[name],[kod],[number])
SELECT [id],[name],[kod],[number]
FROM [MSDBS-JKT\TDETest].[Db].[schema].[table] AS source
WHERE NOT EXISTS (
  SELECT 1
  FROM [Db].[schema].[table] AS target
  WHERE target.[kod] = source.[kod]
  AND target.[nazev] = source.[nazev]
);

报错信息：

Msg 2627, Level 14, State 1, Line 30
Violation of PRIMARY KEY constraint 'PK_ui'. Cannot insert duplicate key in object 'schema.table'. The duplicate key value is (37788, Jan 1 1800 14:00AM).

问题原因 #

1. EXCEPT的逻辑是对比所有查询列的完全匹配，而报错显示主键是复合键（id+时间列）。如果源表中存在主键相同但其他列（如name/number）不同的行，EXCEPT会判定为不同行并尝试插入，而目标库已存在该主键的行，触发冲突。
2. 第二种脚本的NOT EXISTS条件用了kod和nazev（非主键列）做匹配，无法准确判断目标库是否已存在对应主键的行，导致误插入主键重复的记录。

解决方案 #

1. 修正NOT EXISTS脚本：基于主键列判断 #

核心是用主键的所有组成列作为匹配条件，确保只插入目标库中不存在的主键行：

INSERT INTO [Db].[schema].[table] ([id],[name],[kod],[number])
SELECT [id],[name],[kod],[number]
FROM [MSDBS-JKT\TDETest].[Db].[schema].[table] AS source
WHERE NOT EXISTS (
  SELECT 1
  FROM [Db].[schema].[table] AS target
  -- 替换为实际的主键列，这里根据报错推断是id+时间列
  WHERE target.[id] = source.[id]
    AND target.[your_date_column] = source.[your_date_column]
);

2. 使用MERGE语句（支持插入+更新同步） #

如果需要同步数据（不仅插入新行，还更新目标库中主键存在但其他列不同的行），MERGE是更安全的选择：

MERGE [Db].[schema].[table] AS target
USING [MSDBS-JKT\TDETest].[Db].[schema].[table] AS source
ON (target.[id] = source.[id] AND target.[your_date_column] = source.[your_date_column]) -- 主键匹配
WHEN NOT MATCHED THEN
  INSERT ([id],[name],[kod],[number])
  VALUES (source.[id], source.[name], source.[kod], source.[number])
WHEN MATCHED THEN
  UPDATE SET
    target.[name] = source.[name],
    target.[kod] = source.[kod],
    target.[number] = source.[number];

3. 先排查冲突数据（可选） #

如果想先确认哪些行导致冲突，可以先执行查询：

SELECT 
  source.[id], 
  source.[your_date_column], 
  source.[name], 
  source.[kod], 
  source.[number],
  target.[name] AS target_name,
  target.[kod] AS target_kod,
  target.[number] AS target_number
FROM [MSDBS-JKT\TDETest].[Db].[schema].[table] AS source
JOIN [Db].[schema].[table] AS target
ON target.[id] = source.[id] AND target.[your_date_column] = source.[your_date_column]
-- 对比非主键列是否存在差异
WHERE target.[name] <> source.[name] 
   OR target.[kod] <> source.[kod] 
   OR target.[number] <> source.[number];

注意事项 #

• 必须明确表的主键完整组成，所有主键列都要包含在匹配条件中，不能遗漏
• 链接服务器查询时，确保列类型（尤其是时间列的精度）一致，避免隐式转换导致匹配失败
• 增量同步场景，建议添加时间范围过滤，只同步新增/修改的数据，提升执行效率

内容的提问来源于stack exchange，提问作者Jan Kůst