MERGE statement in stored procedure throwing duplicate rows error-CodePudding

I'm using a C# console application to insert some data into a SQL Server table and I'm running into an issue:

System.Data.SqlClient.SqlException: 'The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.'

I've got a method in the C# code to remove any dups and I've also check for duplicate rows in Excel with a formula, I don't see any in my source data. This method returns the same number of records I feed it.

public static DataTable RemoveDuplicatesRecords(DataTable dataTable)
{
    var uniqueRows = dataTable.AsEnumerable().Distinct(DataRowComparer.Default);
    var dataTableOutput = uniqueRows.CopyToDataTable();

    return dataTableOutput;
}

This is the stored procedure:

BEGIN

MERGE INTO Imp_Master_DSDCostPrice AS Target
USING @UpdateRecords AS Source ON Target.ItemUPC = Source.ItemUPC 
                               AND Target.Store = Source.Store 
                               AND Target.RoutingEffectiveDate = Source.RoutingEffectiveDate

WHEN MATCHED THEN
    UPDATE SET 
        Target.APVendor = Source.[APVendor],                                
        Target.POVendor = Source.[POVendor],
        Target.[ArticleNo] = Source.[ArticleNo],
        Target.[CostGroup] = Source.[CostGroup],
        Target.Store = Source.[Store],
        Target.[CostPrice] = Source.[CostPrice],
        Target.[ItemPack] = Source.[ItemPack],
        Target.[CostEffectiveDate] = Source.[CostEffectiveDate],
        Target.[CostEndDate] = Source.[CostEndDate],
        Target.[RetailPrice] = Source.[RetailPrice],
        Target.[RetailEffectiveDate] = Source.[RetailEffectiveDate],
        Target.[RetailEndDate] = Source.[RetailEndDate],
        Target.[DSDTableNo] = Source.[DSDTableNo],
        Target.[OrgGroupNo] = Source.[OrgGroupNo]
       
WHEN NOT MATCHED THEN           
    INSERT (APVendor,
        POVendor,
        ItemUPC,            
        ArticleNo,
        CostGroup,
        Store,
        RoutingEffectiveDate,
        RoutingEndDate,
        CostPrice,
        ItemPack,
        CostEffectiveDate,
        CostEndDate,
        RetailPrice,
        RetailEffectiveDate,
        RetailEndDate,
        DSDTableNo,
        OrgGroupNo
        )
    VALUES (Source.APVendor,
        Source.POVendor,
        Source.ItemUPC,
        Source.ArticleNo,
        Source.CostGroup,
        Source.Store,
        Source.RoutingEffectiveDate,
        Source.RoutingEndDate,
        Source.CostPrice,
        Source.ItemPack,
        Source.CostEffectiveDate,
        Source.CostEndDate,
        Source.RetailPrice,
        Source.RetailEffectiveDate,
        Source.RetailEndDate,
        Source.DSDTableNo,
        Source.OrgGroupNo
        );
END

The columns of ItemUPC, Store, and RoutingEffectiveDate make up the primary key in the table.

Is there a better way to approach the SQL side here?

CodePudding user response：

Removing complete duplicate rows from the DataTable is not enough. You must ensure there is only one row in the DataTable for the match condition.

 ON Target.ItemUPC = Source.ItemUPC 
AND Target.Store = Source.Store 
AND Target.RoutingEffectiveDate = Source.RoutingEffectiveDate

You can do this in SQL Server by merging from a CTE that dedups the input, something like:

with q as 
(
  select *, row_number() over (partition by ItemUPC, Store order by RoutingEffectiveDate desc) rn
  from @UpdateRecords
), q2 as
(
  select * from q where rn = 1
)
MERGE INTO Imp_Master_DSDCostPrice AS Target
USING q2 AS Source . . .