如何仅插入目标表中不存在的字段组合记录？SQL问题排查

需要将源表中ClientID与Name字段组合不存在于目标表dbo.Locations的记录插入该表，需插入的示例记录为：

107 Allentown
107 Annville
107 Baltimore

当前使用的INSERT语句存在部分记录未插入的情况，且目标表已对ClientID和Name组合设置唯一约束，求最优实现方式及问题排查方法。

当前使用的语句：

INSERT INTO dbo.Locations (ClientID, name)
SELECT DISTINCT CL.ClientID, TC.[name]
FROM TMPCTable TC 
JOIN CLIENTS        CL ON TC.[Client Code] = CL.[ClientCode]
LEFT JOIN locations L1 ON L1.Location      = TC.[Name] AND L1.ClientID = CL.CLIENTID
WHERE l1.id IS NULL 

最优实现方式 #

方式1：使用NOT EXISTS（推荐，逻辑清晰性能更优） #

NOT EXISTS在判断存在性时通常比LEFT JOIN + IS NULL高效，尤其当目标表有索引时：

INSERT INTO dbo.Locations (ClientID, Name)
SELECT DISTINCT CL.ClientID, TC.[Name]
FROM TMPCTable TC
JOIN CLIENTS CL ON TC.[Client Code] = CL.ClientCode
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.Locations L
    WHERE L.ClientID = CL.ClientID
      AND L.Name = TC.[Name] -- 注意匹配目标表实际字段名，原语句用L1.Location可能是错误点
)

方式2：使用EXCEPT语句 #

自动去重，无需额外DISTINCT，适合直接对比字段组合的场景：

INSERT INTO dbo.Locations (ClientID, Name)
SELECT CL.ClientID, TC.[Name]
FROM TMPCTable TC
JOIN CLIENTS CL ON TC.[Client Code] = CL.ClientCode
EXCEPT
SELECT ClientID, Name
FROM dbo.Locations

方式3：使用MERGE语句（SQL Server 2008+） #

适合需要同时处理插入/更新的场景，逻辑更完整：

MERGE INTO dbo.Locations AS Target
USING (
    SELECT DISTINCT CL.ClientID, TC.[Name]
    FROM TMPCTable TC
    JOIN CLIENTS CL ON TC.[Client Code] = CL.ClientCode
) AS Source 
ON Target.ClientID = Source.ClientID AND Target.Name = Source.Name
WHEN NOT MATCHED THEN
    INSERT (ClientID, Name)
    VALUES (Source.ClientID, Source.Name);

当前语句问题排查步骤 #

1. 核对字段名匹配
   原语句左连接用L1.Location = TC.[Name]，确认目标表dbo.Locations的名称字段是Location还是Name？如果字段名不匹配，会直接导致存在性判断错误。

2. 验证源表关联结果
   单独执行SELECT DISTINCT CL.ClientID, TC.[name] FROM TMPCTable TC JOIN CLIENTS CL ON TC.[Client Code] = CL.ClientCode，检查结果是否包含需要插入的所有示例记录，若缺失则说明源表关联逻辑本身有问题。

3. 确认唯一约束字段组合
   检查目标表的唯一约束是否确实是(ClientID, Name)，如果约束字段是其他组合（比如ClientID + Location），会导致插入时触发约束报错但未被捕获。

4. 排查字符匹配问题

   • 检查TC.[Name]与目标表对应字段的字符类型是否一致，是否存在隐式转换；
   • 排查前后空格或大小写差异：可以用LTRIM(RTRIM(TC.[Name])) = LTRIM(RTRIM(L1.Name))临时替换原条件，验证是否是空格导致的匹配失败；
   • 若数据库是区分大小写的排序规则，需确认字符大小写是否统一。

5. 分步执行查询验证
   执行分步查询：

   SELECT CL.ClientID, TC.[name], L1.id 
   FROM TMPCTable TC 
   JOIN CLIENTS CL ON TC.[Client Code] = CL.ClientCode
   LEFT JOIN locations L1 ON L1.Location = TC.[Name] AND L1.ClientID = CL.CLIENTID
   

   查看需要插入的记录对应的L1.id是否为NULL，若不为NULL则说明左连接条件错误匹配了现有记录。

6. 检查事务与锁问题
   确认是否有未提交事务导致目标表数据未被读取，或插入操作被锁阻塞，导致部分记录未写入。

7. 查看错误日志
   开启SET NOCOUNT OFF和SET XACT_ABORT ON后执行插入语句，查看是否有隐藏错误；同时检查SQL Server错误日志，排查是否有约束冲突等未被捕获的异常。

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