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

MERGE WHEN MATCHED

MERGE statement can be use to modify data in a target table based on data in a source table. Merge joins the target to the source by using a column common in both tables, such as parent-child key column. You can then insert, modify, or delete data from the target table, all in one statement, according to how the rows match up as a result of the join.

The MERGE statement comes up with many clauses that facilitate the different types of data modifications.

In the first part of article we will look the When MATCHED clause later in next part we will see When NOT MATCHED [Source]/[Target] .

To facilitate our article, let’s take an example and demonstrate how it work, we are here creating two tables MarblesCounts_KancheKhelega as target table and MarblePurchased_ChalKancheLene as source table

create table dbo.MarblesCounts_KancheKhelega  -- target

(

  marble_id int not null primary key,

  marble_color varchar(40) not null ,

  marble_count int not null

    constraint MarbleCount_Default_1 default 0

);

Command(s) completed successfully.

create table dbo.MarblePurchased_ChalKancheLene  -- source

(

  marble_id int not null primary key,

  marble_color varchar(40) not null,

  marble_count int not null

    constraint MarbleCount_Default_2 default 0

);

Tables are in place let’s inserts few records in it.

insert into MarblesCounts_KancheKhelega values

  (1, 'White wale bade Kanche', 6),

  (2, 'Pale Yellow wale Dimond Kanche', 3),

  (3, 'Red Ramesh ke kanche', 0),

  (5, 'Green Suresh ke churaye kanche', 0),

  (6, 'Purple Kamal se jite kanche', 0),

  (8, 'Blue har game jitane wala lucky kanche', 4);

(6 row(s) affected)

insert MarblePurchased_ChalKancheLene values

  (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);

(5 row(s) affected)

Querying the table to see the inserted records.

select * from  MarblesCounts_KancheKhelega

marble_id   marble_color                             marble_count

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

1           White wale bade Kanche                   6

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

5           Green Suresh ke churaye kanche           0

6           Purple Kamal se jite kanche              0

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

Since we have table with data pupulated, now it is the time to jump into MERGE and see how it works

WHEN MATCHED Clause

The first MERGE clause we’ll look at is WHEN MATCHED. You should use this clause when you want to update or delete rows in the target table that match rows in the source table. Rows are considered matching when the joined column values are the same.

For example, if the marble_id value in the MarblesCounts_KancheKhelega table matches the marble_id  value in the MarblePurchased_ChalKancheLene table, the rows are considered to match, regardless of the other values in the matching rows. When rows do match, you can use the WHEN MATCHED clause to modify data in the target table. Lets look at an example to demonstrate how this works.

In the following MERGE statement, I join the MarblesCounts_KancheKhelega table (the target) to the MarblePurchased_ChalKancheLene table (the source) and then use a WHEN MATCHED clause to update the marble_count column in the target table:

merge MarblesCounts_KancheKhelega kc

using MarblePurchased_ChalKancheLene ckk

on kc.marble_id = ckk.marble_id

when MATCHED then

  update

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

select * from MarblesCounts_KancheKhelega;

marble_id   marble_color                             marble_count

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

1           White wale bade Kanche                   9

2           Pale Yellow wale Dimond Kanche           3

3           Red Ramesh ke kanche                     0

5           Green Suresh ke churaye kanche           5

6           Purple Kamal se jite kanche              0

8           Blue har game jitane wala lucky kanche   4

(6 row(s) affected)

Suppose you want to remove from the MarblesCounts_KancheKhelega table any marble whose Quantity value is 0 in both the target and source tables. You can easily delete such rows by adding a second WHEN MATCHED clause to your MATCH statement, as shown in the following example

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;

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

(5 row(s) affected)

So here we can see those records whose marble_id matched in both the table and whose sum of marble count in MarblesCounts_KancheKhelega and MarblePurchased_ChalKancheLene are removed.

Programming is the fuel of Saturday and Sunday 

Post Reference: Vikram Aristocratic Elfin Share