c# - Issue Understanding SQL Server Merge for Bulk Insert

Question

I generate a DataTable and I first remove all rows from that DataTable in C#.

Then I pass on the DataTable to a stored procedure for the bulk insert but I am getting error randomly stating:

The Merge statement updated /delete same row more than once. This happens when a target row matches more than one source row.

But what is confusing me is I remove all duplicate rows from the DataTable before sending it to the stored procedure.

CODE:

public static DataTable RemoveDuplicateRows(DataTable dTable, string colName)
{
    Hashtable hTable = new Hashtable();
    ArrayList duplicateList = new ArrayList();

    //Add list of all the unique item value to hashtable, which stores combination of key, value pair.
    //And add duplicate item value in arraylist.
    foreach (DataRow drow in dTable.Rows)
    {
        if (hTable.Contains(drow[colName]))
            duplicateList.Add(drow);
        else
            hTable.Add(drow[colName], string.Empty);
    }

    //Removing a list of duplicate items from datatable.
    foreach (DataRow dRow in duplicateList)
        dTable.Rows.Remove(dRow);

    //Datatable which contains unique records will be return as output.
    return dTable;
}

And this is the procedure:

ALTER PROCEDURE [dbo].[Update_DataFeed_Discoverable]
  @tblCustomers DateFeed_Discoverable READONLY
  AS
  BEGIN
  SET NOCOUNT ON;

  MERGE INTO DataFeed c1
  USING @tblCustomers c2
  ON c1.CheckSumProductName=checksum(c2.aw_deep_link)
  WHEN MATCHED THEN
  UPDATE SET 
        c1.merchant_name = c2.merchant_name
        ,c1.aw_deep_link = c2.aw_deep_link
        ,c1.brand_name = c2.brand_name
        ,c1.product_name = c2.product_name
        ,c1.merchant_image_url = c2.merchant_image_url
        ,c1.Price = c2.Price

  WHEN NOT MATCHED THEN
  INSERT VALUES(c2.merchant_name, c2.aw_deep_link,
  c2.brand_name, c2.product_name, c2.merchant_image_url,
  c2.Price,1,checksum(c2.aw_deep_link)

  );
END

So does it end up with duplicate rows when they are not Inserted and removed before using the DataTable.

Any help is appreciated.

Answer 1

等待大神答复