MERGE WHEN NOT MATCHED

MERGE WHEN NOT MATCHED BY TARGET

Refer Previous Post before reading this: MERGE WHEN MATCHED

You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target. For example, the BookOrder table contains a row for Gone with the Wind. However, the BookInventory table does not contain this book. The following example demonstrates how to include a WHEN NOT MATCHED clause in your MERGE statement that adds Gone with the Wind to your target table:

Let see first what is there in the target table

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   12

2           Pale Yellow wale Dimond Kanche           3

5           Green Suresh ke churaye kanche           10

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

What is there in source table

select * from MarblePurchased_ChalKancheLene

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   12

2           Pale Yellow wale Dimond Kanche           3

5           Green Suresh ke churaye kanche           10

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

Now if we apply MERGE when NOT MATCHED by TARGET then it should insert all the rows which are present in source (MarblePurchased_ChalKancheLene) but not there in TARGET (MarblesCounts_KancheKhelega)

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED AND

  kc.marble_count + ckk.marble_count = 0 then

  delete

when MATCHED then

  update

  set kc.marble_count = kc.marble_count + ckk.marble_count

when NOT MATCHED by TARGET then

  insert (marble_id, marble_color, marble_count)

  values (ckk.marble_id, ckk.marble_color,ckk.marble_count);

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   15

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           15

6           Purple Kamal se jite kanche              0

7           Silver bade expensive Kanche             8

8           Blue har game jitane wala lucky kanche   4

(8 row(s) affected)

So here we can see the apply of MERGE when NOT MATCHED by TARGET  insert all the rows which were present in source (MarblePurchased_ChalKancheLene) but not there in TARGET (MarblesCounts_KancheKhelega)  in target table(MarblesCounts_KancheKhelega)  .

MERGE WHEN NOT MATCHED BY SOURCE

suppose you want to delete a row from the target table that does not match a row in the source table. To delete a row that does not match a row in the source table, you must use the WHEN NOT MATCHED BY SOURCE clause.

Lets see what rows are there in source (MarblePurchased_ChalKancheLene) and target table(MarblesCounts_KancheKhelega).  

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   15

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           15

6           Purple Kamal se jite kanche              0

7           Silver bade expensive Kanche             8

8           Blue har game jitane wala lucky kanche   4

select * from MarblePurchased_ChalKancheLene

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   3

3           Red Ramesh ke kanche                     0

4           Grey bade unlucky kanche                 4

5           Green Suresh ke churaye kanche           5

7           Silver bade expensive Kanche             8

The following example includes a WHEN NOT MATCHED BY SOURCE clause that specifies that any rows with a quantity of 0 that do not match the source should be deleted:

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED AND

  kc.marble_count + ckk.marble_count = 0 then

  delete

when MATCHED then

  update

  set kc.marble_count = kc.marble_count + ckk.marble_count

when NOT MATCHED by TARGET then

  insert (marble_id, marble_color, marble_count)

  values (ckk.marble_id, ckk.marble_color,ckk.marble_count)

when NOT MATCHED by SOURCE

  AND kc.marble_count = 0 then

  delete;

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

----------- ---------------------------------------- ------------

1           White wale bade Kanche                   18

2           Pale Yellow wale Dimond Kanche           3

4           Grey bade unlucky kanche                 8

5           Green Suresh ke churaye kanche           20

7           Silver bade expensive Kanche             16

8           Blue har game jitane wala lucky kanche   4

(6 row(s) affected)

A MERGE statement can simplify your code, improve performance, and reduce your development effort.

Fuel up you; start code for tomorrow, people are waiting  

Post Reference: Vikram Aristocratic Elfin Share