如何解决SQL Merge报错Msg 8672且源表无重复行的问题

问题根因 #

你当前使用的匹配键RecID在源表Staging.ConfigurationItem中已不具备全局唯一性：该表由Circuit、Service Item两个原始表合并而来，两个原始表各自的RecID唯一，但跨表存在重复的RecID，导致执行MERGE时，目标表的单行数据匹配到源表的多行数据，触发报错。

可选解决方案 #

方案1：调整匹配键为联合唯一键（最推荐，适配多业务类型共存场景） #

你的源表中存在ConfigurationItemTypeName字段区分记录所属的业务类型（Circuit/Service Item），将该字段与RecID组成联合匹配键即可解决匹配冲突问题，同时不需要丢弃任何有效数据：

• 修改MERGE语句的ON子句为：

ON [Source].[RecID] = [Target].[RecID] 
AND [Source].[ConfigurationItemTypeName] = [Target].[ConfigurationItemTypeName]

注意：需要同步确认目标表Cherwell.ConfigurationItem的主键为RecID + ConfigurationItemTypeName联合主键，否则插入时会触发主键冲突。

方案2：源表提前按RecID去重，仅保留最新记录 #

如果业务上同RecID仅需要保留最后修改时间最新的一条记录，可以在USING子句中对源表做预处理，过滤重复的RecID：

USING (
    SELECT * FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (
                PARTITION BY RecID 
                ORDER BY CONVERT(DATETIME, LastModifiedDateTime) DESC
            ) AS row_num
        FROM [Staging].[ConfigurationItem]
    ) AS source_filter
    WHERE row_num = 1
) AS [Source]

修改后完整存储过程示例（采用方案1） #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [Staging].[uspMergeCherwellComConfigurationItem]
AS
BEGIN
    MERGE [Cherwell].[ConfigurationItem] AS [Target]
    USING [Staging].[ConfigurationItem] AS [Source]
    -- 调整为联合匹配键
    ON [Source].[RecID] = [Target].[RecID] 
    AND [Source].[ConfigurationItemTypeName] = [Target].[ConfigurationItemTypeName]
    WHEN MATCHED AND CONVERT(DATETIME, [Source].[LastModifiedDateTime]) <> [Target].[LastModifiedDateTime] 
    THEN
        UPDATE SET [Target].[PartitionKey] = [Source].[PartitionKey],
                   [Target].[RecID] = [Source].[RecID],
                   [Target].[ConfigurationItemTypeName] = [Source].[ConfigurationItemTypeName],
                   [Target].[CreatedDateTime] = [Source].[CreatedDateTime],
                   [Target].[Status] = [Source].[Status],
                   [Target].[CIType] = [Source].[CIType],
                   [Target].[FriendlyName] = [Source].[FriendlyName],
                   [Target].[FinancialReference] = [Source].[FinancialReference],
                   [Target].[OrganisationGroup] = [Source].[OrganisationGroup],
                   [Target].[Further_Detail] = [Source].[Further_Detail],
                   [Target].[CICount] = [Source].[CICount],
                   [Target].[unit] = [Source].[unit],
                   [Target].[LastModifiedDateTime] = CONVERT(DATETIME, [Source].[LastModifiedDateTime])
    WHEN NOT MATCHED THEN
        INSERT
        ([PartitionKey],
            [RecID],
            [ConfigurationItemTypeName],
            [CreatedDateTime],
            [LastModifiedDateTime],
            [Status],
            [CIType],
            [FriendlyName],
            [FinancialReference],
            [OrganisationGroup],
            [Further_Detail],
            [CICount],
            [unit]
        )
        VALUES
        ([Source].[PartitionKey], [Source].[RecID], [Source].[ConfigurationItemTypeName], [Source].[CreatedDateTime],
         CONVERT(DATETIME, [Source].[LastModifiedDateTime]), [Source].[Status], [Source].[CIType], [Source].[FriendlyName],
         [Source].[FinancialReference], [Source].[OrganisationGroup],[Source].[Further_Detail], [Source].[CICount], [Source].[unit]);
END;

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