如何从源表批量插入/更新数据至多张目标表？

先解决ClassTbl的MERGE重复问题 #

你当前的MERGE语句出现重复插入，核心原因有两个：

1. 源表SourceTbl中存在重复的ClassCode记录，导致MERGE时同一目标行匹配到多条源行，触发异常或错误插入
2. UPDATE语句存在字段拼写错误：Source.[ClassDescrition]应为Source.[ClassDescription]

修正后的ClassTbl MERGE语句，先对源表去重：

MERGE ClassTbl AS Target
USING (
    -- 对源表ClassCode去重，避免重复源数据引发的问题
    SELECT DISTINCT
        ClassCode,
        ClassDescription
    FROM SourceTbl
    WHERE ClassCode IS NOT NULL -- 过滤空值，避免无效匹配
) AS Source
ON Source.ClassCode = Target.Code
WHEN NOT MATCHED BY Target
THEN INSERT (Code, Description)
     VALUES (Source.ClassCode, Source.ClassDescription)
WHEN MATCHED AND ISNULL(Source.ClassDescription, '') <> ISNULL(Target.Description, '') -- 处理NULL值相等的情况
THEN UPDATE SET
    Target.Description = Source.ClassDescription;

批量处理CompanyTbl的插入/更新 #

沿用相同逻辑处理CompanyTbl，注意字段映射的准确性：

MERGE CompanyTbl AS Target
USING (
    SELECT DISTINCT
        CompanyCode,
        CompanyDescription,
        CompanyOtherColumn
    FROM SourceTbl
    WHERE CompanyCode IS NOT NULL
) AS Source
ON Source.CompanyCode = Target.Code
WHEN NOT MATCHED BY Target
THEN INSERT (Code, Description, OtherColumn)
     VALUES (Source.CompanyCode, Source.CompanyDescription, Source.CompanyOtherColumn)
WHEN MATCHED AND (
    ISNULL(Source.CompanyDescription, '') <> ISNULL(Target.Description, '')
    OR ISNULL(Source.CompanyOtherColumn, '') <> ISNULL(Target.OtherColumn, '')
) -- 任意字段有变化则执行更新
THEN UPDATE SET
    Target.Description = Source.CompanyDescription,
    Target.OtherColumn = Source.CompanyOtherColumn;

关键注意事项 #

• 源表去重：必须确保用于匹配的字段（ClassCode、CompanyCode）无重复，否则MERGE会因"源表多行匹配目标单行"抛出错误，同时避免数据混乱
• NULL值处理：用ISNULL或COALESCE处理NULL值，防止因两边均为NULL被误判为不相等
• 字段校验：严格对应源表与目标表的字段，避免拼写错误
• 唯一性约束：给目标表的Code字段添加唯一约束，从根源上阻止重复插入：

-- 为ClassTbl的Code字段添加唯一约束
ALTER TABLE ClassTbl ADD CONSTRAINT UQ_ClassTbl_Code UNIQUE (Code);

-- 为CompanyTbl的Code字段添加唯一约束
ALTER TABLE CompanyTbl ADD CONSTRAINT UQ_CompanyTbl_Code UNIQUE (Code);

内容的提问来源于stack exchange，提问作者JF-Mechs