IF EXISTS and MERGE Statement

Question

I have data flowing into one table from multiple other tables lets say: Table_A Then I have a Merge stored proc that takes data from table A merges it with Table B.

However, something doesn't seem to be right. If i truncate and load the data it works fine, but if i dont truncate and load, and just fetch the query by eachh hour I get the error message saying

Msg 8672, Level 16, State 1, Procedure Merge_Table_A, Line 4 [Batch Start Line 0] 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.

How can I overcome this?

I want to be able to incrementally load the data and not do truncate loads, but at the same time have a stored proc that updates or inserts or doesnt care if the row already exists.

Answer 1

Seems you have duplicate rows in your target table which are loaded from your previous runs.

Note: Matching in a Merge does not consider the rows inserted (even duplicate) while running the Merge itself.

Below is my repro example with a sample data:

Table1: Initial data

Table2: Taget table

Merge Statement:

MERGE tb2 AS Target
USING tb1   AS Source
 ON Source.firstname = Target.firstname and 
    Source.lastname = Target.lastname 

-- For Inserts
WHEN NOT MATCHED BY Target THEN
INSERT (firstname, lastname, updated_date) 
VALUES (Source.firstname, Source.lastname, source.updated_date)

-- For Updates
WHEN MATCHED THEN UPDATE SET
   Target.updated_date      = Source.updated_date

-- For Deletes
WHEN NOT MATCHED BY Source THEN
DELETE;

When Merge is executed, it inserts all data without any errors.

New data in tb1:

When I run the Merge statement, it gives me the same error as yours.

As a workaround using one of the below options,

1. Add additional conditions if possible in the ON clause to uniquely identify the data.

2. Remove the duplicates from the source and merge the data into tb2 as below.

    --temp table
    drop table if exists #tb1;
   
    select *  into #tb1 from (
        select *, row_number() over(partition by firstname, lastname order by firstname, lastname, updated_date desc) as rn from tb1) a
    where rn = 1 
   
    MERGE tb2 AS Target
    USING #tb1 AS Source
     ON Source.firstname = Target.firstname and 
        Source.lastname = Target.lastname 
   
    -- For Inserts
    WHEN NOT MATCHED BY Target THEN
    INSERT (firstname, lastname, updated_date) 
      VALUES (Source.firstname, Source.lastname, source.updated_date)
   
    -- For Updates
    WHEN MATCHED THEN UPDATE SET
       Target.updated_date     = Source.updated_date
   
    -- For Deletes
    WHEN NOT MATCHED BY Source THEN
      DELETE;
   

Data merged into tb2 successfully.