SQL MERGE语句无法匹配，无法更新新增字段问题求助

我是SQL新手，接手现有代码进行修改，要给某存储过程的输出添加4个新字段。已确认#TotalDocSales包含这些新字段，但MERGE语句无法将其同步到#PromoDiscountSales。人工核对两张表后，认为ON子句中的数据应该可以匹配，我已经在SELECT语句中添加了CONVERT函数尝试确保两表数据类型完全对齐，不确定是否必要，仅作为排查起点。

问题代码 #

MERGE INTO #PromoDiscountSales AS T
USING (
        SELECT  CONVERT(date, Dttransdate) AS DtTransDate, 
                r.WeekENDing, 
                r.MonthName, 
                r.MonthPeriod, 
                r.FYE, 
                CONVERT(varchar(12), DocNumber) As DocNumber, 
                CONVERT(varchar(5), DocType) AS DocType,  
                CONVERT(int, LocationNum) AS LocationNum, 
                l.LocationNumName, 
                l.Region, 
                StoreType, 
                CONVERT(varchar(25), ItemNum) AS ItemNum, 
                LTRIM(RTRIM(Description)) As ItemDescription, 
                PromoItemNum, 
                LTRIM(RTRIM(PromoItemDesc)) AS PromoItemDescription, 
                CONVERT(int, VendorNum) AS VendorNum, 
                VendorName ,
                CONVERT(int, DepartmentNum) As DepartmentNum, 
                CONVERT(varchar(25), ItemGroup) AS ItemGroup,
                CONVERT(varchar(25), PromoAppliedOn) AS PromoAppliedOn, 
                CONVERT(varchar(200), PromoAppliedOnVendor) AS PromoAppliedOnVendor, 
                TotalSales, 
                COGS, 
                QtySold, 
                DiscountAmt as DiscountAmount, 
                TotalSalesAfterDisc as NetTotalSales, 
                NoOfPromoQty, 
                RetailPrice, 
                PromoSalesPrice, 
                GrossSales,
                RedTagItemCode,
                VendorsItemNum,
                PosSlsprsnNum1,
                PosSlsprsnNum2,
                PosSlsprsnNum3

        FROM   #TotalDocSales t
        JOIN C_PRODUCTION.dbo.TU_MD_RETAILCALENDAR r with (nolock) 
            ON t.DtTransDate = r.Date
        JOIN C_PRODUCTION.dbo.TU_MD_LOCATION l with (nolock) 
            ON t.LocationNum = l.LocNum
        WHERE  (TotalSales <> 0 
                OR COGS <> 0 
                OR QtySold <> 0 
                OR QtySold <> 0 
                OR TotalSalesAfterDisc <> 0 
                OR RetailPrice <> 0 
                OR PromoSalesPrice <> 0 
                OR GrossSales <> 0)

) AS S
ON T.DtTransDate = S.DtTransDate 
   AND .LocationNum = S.LocationNum  -- 语法错误：缺少表别名
   AND T.DocNumber = S.DocNumber 
   AND T.DocType = S.DocType 
   AND LTRIM(RTRIM(T.ItemNum)) = LTRIM(RTRIM(S.ItemNum)) 
   AND T.VendorNum  = S.VendorNum  
   AND LTRIM(RTRIM(T.ItemGroup)) = LTRIM(RTRIM(S.ItemGroup)) 
   AND T.DepartmentNum = S.DepartmentNum
   AND LTRIM(RTRIM(T.PromoAppliedOn)) = LTRIM(RTRIM(S.PromoAppliedOn))
   AND LTRIM(RTRIM(ISNULL(T.PromoAppliedOnVendor,''))) = LTRIM(RTRIM(ISNULL(S.PromoAppliedOnVendor,'')))

WHEN  MATCHED 
THEN UPDATE
SET 
    VendorsItemNum = S.VendorsItemNum,
    PosSlsprsnNum1 = S.PosSlsprsnNum1,
    PosSlsprsnNum2 = S.PosSlsprsnNum2,
    PosSlsprsnNum3 = S.PosSlsprsnNum3,
    UpdatedDate = GETDATE()

WHEN  NOT MATCHED BY TARGET THEN INSERT

    (   DtTransDate, 
        WeekENDing, 
        MonthName, 
        MonthPeriod, 
        FYE, 
        DocNumber, 
        DocType, 
        LocationNum, 
        LocationNumName, 
        Region, 
        StoreType, 
        ItemNum, 
        RedTagItemCode,
        ItemDescription, 
        PromoItemNum, 
        PromoItemDescription, 
        VendorNum , 
        VendorName , 
        DepartmentNum, 
        ItemGroup, 
        PromoAppliedOn, 
        PromoAppliedOnVENDor,  -- 字段大小写与源表不一致
        TotalSales, 
        COGS, 
        QtySold, 
        RetailPrice, 
        PromoSalesPrice, 
        DiscountAmount, 
        NetTotalSales, 
        NoOfPromoQty, 
        GrossSales, 
        VendorsItemNum,
        PosSlsprsnNum1,
        PosSlsprsnNum2,
        PosSlsprsnNum3,
        LoadedDate)

    VALUES (
        S.DtTransDate, 
        S.WeekENDing, 
        S.MonthName, 
        S.MonthPeriod, 
        s.FYE, 
        s.DocNumber, 
        s.DocType, 
        s.LocationNum, 
        s.LocationNumName, 
        s.Region, 
        s.StoreType, 
        s.ItemNum, 
        s.RedTagItemCode,
        s.ItemDescription, 
        s.PromoItemNum, 
        s.PromoItemDescription, 
        s.VendorNum , 
        s.VendorName ,
        s.DepartmentNum, 
        s.ItemGroup, 
        s.PromoAppliedOn, 
        s.PromoAppliedOnVENDor,  -- 字段大小写与源表不一致
        s.TotalSales, 
        s.COGS, 
        s.QtySold, 
        s.RetailPrice,
        s.PromoSalesPrice, 
        s.DiscountAmount, 
        s.NetTotalSales, 
        s.NoOfPromoQty,
        s.GrossSales, 
        s.VendorsItemNum,
        s.PosSlsprsnNum1,
        s.PosSlsprsnNum2,
        s.PosSlsprsnNum3,   
        GETDATE());

排查与修复步骤 #

• 修复语法错误：ON子句中的.LocationNum缺少表别名，应改为T.LocationNum = S.LocationNum，这是MERGE无法正常执行的直接原因。
• 统一字段大小写：INSERT部分的PromoAppliedOnVENDor改为PromoAppliedOnVendor，避免因数据库排序规则区分大小写导致字段映射失败。
• 验证匹配数据量：运行以下查询，确认实际能匹配的行数，排除人工核对误差：

SELECT COUNT(*) AS MatchedCount
FROM #PromoDiscountSales T
JOIN (
    SELECT  CONVERT(date, Dttransdate) AS DtTransDate, 
            CONVERT(int, LocationNum) AS LocationNum, 
            CONVERT(varchar(12), DocNumber) As DocNumber, 
            CONVERT(varchar(5), DocType) AS DocType,  
            CONVERT(varchar(25), ItemNum) AS ItemNum, 
            CONVERT(int, VendorNum) AS VendorNum, 
            CONVERT(int, DepartmentNum) As DepartmentNum, 
            CONVERT(varchar(25), ItemGroup) AS ItemGroup,
            CONVERT(varchar(25), PromoAppliedOn) AS PromoAppliedOn, 
            CONVERT(varchar(200), PromoAppliedOnVendor) AS PromoAppliedOnVendor
    FROM   #TotalDocSales t
    JOIN C_PRODUCTION.dbo.TU_MD_RETAILCALENDAR r with (nolock) 
        ON t.DtTransDate = r.Date
    JOIN C_PRODUCTION.dbo.TU_MD_LOCATION l with (nolock) 
        ON t.LocationNum = l.LocNum
    WHERE  (TotalSales <> 0 
            OR COGS <> 0 
            OR QtySold <> 0 
            OR TotalSalesAfterDisc <> 0 
            OR RetailPrice <> 0 
            OR PromoSalesPrice <> 0 
            OR GrossSales <> 0)
) S ON T.DtTransDate = S.DtTransDate 
   AND T.LocationNum = S.LocationNum 
   AND T.DocNumber = S.DocNumber 
   AND T.DocType = S.DocType 
   AND LTRIM(RTRIM(T.ItemNum)) = LTRIM(RTRIM(S.ItemNum)) 
   AND T.VendorNum  = S.VendorNum  
   AND LTRIM(RTRIM(T.ItemGroup)) = LTRIM(RTRIM(S.ItemGroup)) 
   AND T.DepartmentNum = S.DepartmentNum
   AND LTRIM(RTRIM(T.PromoAppliedOn)) = LTRIM(RTRIM(S.PromoAppliedOn))
   AND LTRIM(RTRIM(ISNULL(T.PromoAppliedOnVendor,''))) = LTRIM(RTRIM(ISNULL(S.PromoAppliedOnVendor,'')))

• 核对表结构：确认#PromoDiscountSales中新增的4个字段数据类型与#TotalDocSales完全一致，避免类型不兼容导致同步失败。
• 查看执行统计：执行MERGE后，通过SELECT @@ROWCOUNT查看受影响行数，确认是匹配失败还是更新/插入逻辑未触发。

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