Explain Merge SQL Statement in Simple Words?

Question

I am reading the msdn topic about MERGE.http://msdn.microsoft.com/en-us/library/bb510625.aspx

But it is very confusing for me. Let's say I have a table,

DECLARE @T Table
(
    ID INt,
    Name VARCHAR(10)
)

and I have,

MERGE INTO @T T1
USING (SELECT 4 AS ID) T2 ON (T1.ID = T2.ID)

WHEN MATCHED THEN will execute every time when a match is found, WHEN NOT MATCHED THEN will execute every time when a match is not found? Is it correct? What about WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED BY Target. Please help me

Answer 1

I think of it in terms of branching records from both the target and source into different execution paths.

Below I give an example of a simple list of numbers. I use a full join to represent the merge and case to represent the 'branching'.

DECLARE @source TABLE ( i INT, c CHAR(1) )
DECLARE @target TABLE ( i INT )

INSERT INTO @source ( i )
VALUES (1), (2), (3), (4), (5)

INSERT INTO @target ( i )
VALUES (1), (2), (3), (6), (7)

SELECT 
    [source] = s.i, 
    [target] = t.i,
    [branch] = CASE WHEN t.i IS NULL THEN 'not matched by target'
                    WHEN s.i IS NULL THEN 'not matched by source'
                    ELSE 'matched' END,
    [possible action] = CASE WHEN t.i IS NULL THEN 'insert into target'
                             WHEN s.i IS NULL THEN 'update target or delete from target'
                             ELSE 'update target or delete from target' END
FROM @source s
FULL JOIN @target t ON t.i = s.i

This produces the following

source      target      branch                 possible action
----------- ----------- ---------------------  -----------------------------------
1           1           matched                update target or delete from target
2           2           matched                update target or delete from target
3           3           matched                update target or delete from target
4           NULL        not matched by target  insert into target
5           NULL        not matched by target  insert into target
NULL        6           not matched by source  update target or delete from target
NULL        7           not matched by source  update target or delete from target

so

• When a source record does not have a match in the target (not matched by target) then these can be inserted into the target
• When a target record does not have a match in the source (not matched by source) then the corresponding target record can be updated or deleted here there obviously won't be any source records to refer to.
• When a source record matched a target record (matched) then the target record can also be updated or deleted but unlike not matched by source here you will also have the records from the source.

Note for the updates and deletes, there is no need to use a join or otherwise relate the source to the target, target to the target etc within a 'branch' as these relationships have already been resolved and it's as though you are acting on an individual record.

e.g. you may think you would have to do an update as

 Update t
 Set t.col = s.col
 From target t
 Join source s On s.id = t.id

but this is not the case.

When a record has been either matched or not matched by source then one can further predicate the data to decide whether or not it should be deleted or updated. This is done by providing two of the same 'branch' with the additional AND clause as shown in example d of MERGE.