How to create Merge SQL when with duplicated data

Question

How to create MERGE SQL with Duplicated Data inside in Target and Source?

I have huge Merge SQL with quite complicated joins, so I created simple example for this question.

Target
ID  Load
1   1
2   2
3   3
3   3
4   4
5   5

Source

ID  Load
1   111
3   333
3   444
6   666


Result should be:

  ID    Load
    1   111
    2   2
    3   444
    3   444
    4   4
    5   5
    6   666

Here is example I made which is very simple version

How to make Merge to be looks very simple?

--1. Create tables

       CREATE TABLE [dbo].[Target](
            [ID] [int] NOT NULL,
            [Load] [INT] NOT NULL
        ) ON [PRIMARY]


        CREATE TABLE [dbo].[Source](
            [ID] [int] NOT NULL,
            [Load] [int] NOT NULL
        ) ON [PRIMARY]

--2. We need to insert data into Target and Source


    --Insert Duplicated Data, where ID: 3 is duplicated
    INSERT INTO dbo.Target (ID, [LOAD]) VALUES (1,1),(2,2),(3,3),(3,3),(4,4),(5,5)

    --We update Load for every match, 
    --We need insert new data as 
    INSERT INTO dbo.Source (ID, [LOAD]) VALUES (1,111),(3,333),(3,444),(6,666)


SELECT * from dbo.Target
SELECT * from dbo.Source

--3. Now Merge

--Expected: 
-- 111 get 111
-- 333 get 444
-- 6 inserted with 666

BEGIN TRANSACTION

MERGE dbo.Target AS T 
USING dbo.Source AS S
ON T.ID = S.ID
WHEN MATCHED THEN
     UPDATE 
        SET T.[Load] = S.[Load] --Update Load to See what is going on
WHEN NOT MATCHED THEN 
    INSERT (ID, [Load])
     VALUES
     (ID,[Load])
    OUTPUT $action,  deleted.*, inserted.*   --INTO #A_Updated
;

ROLLBACK TRANSACTION

--How to make it work, and ignore duplicate data?

Results:

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.

user_50552 · Answer

For a merge update, each row in the target table should match only a single row in the source table. For example, you could take the maximum value of LOAD per ID:

MERGE   dbo.Target AS T 
USING   (
        SELECT  ID
        ,       MAX(LOAD) as LOAD
        FROM    dbo.Source
        GROUP BY
                ID
        ) AS S
ON      T.ID = S.ID

See it working at rextester.

How to create Merge SQL when with duplicated data

1 Answers