CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33964

Explain Merge SQL Statement in Simple Words?

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

Upvotes: 3

Views: 341

Answers (4)

T I
T I

Reputation: 9933

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.

Upvotes: 1

Christian Phillips
Christian Phillips

Reputation: 18749

You take a source table, and try to merge new records into this source table. You set a condition to check based on the source data and the new data. If the condition is true, then this is a match, when not true, this is not matched and each will have it's own statement.

Most commonly used (from sql I've seen/used) when wanting to decide whether you're adding new data to a source with an insert, or updating existing records. For example, product inventory, you might be adding new inventory or updating the count of existing inventory.

There's a good example here to follow https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/

Upvotes: 1

SchmitzIT
SchmitzIT

Reputation: 9552

Merge basically will merge the data of two tables. If an ID field from the source table exists in the target table, it will do an UPDATE statement (WHEN MATCHED THEN). In case it does not exist, it will perform an INSERT (WHEN NOT MATCHED BY TARGET).

WHEN NOT MATCHED BY SOURCE means there's a row in the target table that does not exist in the source table. This usually indicates that the row was deleted in the source system, and you'll have to decide what to do with it. (In case of a transnational system, rows simply are removed, whereas if you're working on a data warehouse, you'd want to keep the data, but mark it as "inactive" or something along those lines).

Prior to having the MERGE statement, one would have to write this logic themselves.

Upvotes: 1

siride
siride

Reputation: 209525

The latter two should be obvious. If a match isn't found, it means one or the other table doesn't have a row where the other table does. That is, T1 might have a row, but there's no corresponding row in T2, or T2 might have a row, but there's no corresponding row in T1. T2 is the source and T1 is the target, so when you say NOT MATCHED BY SOURCE you are specifying the action to take place when T2 does not have a row, and NOT MATCHED BY TARGET is for when T1 does not have a row.

Upvotes: 0

Related Questions