Using MERGE and MATCHED in SQL
I've recently got the lease for a movie mega store and have decided to
consolidate two of my stores into one. In my records I have the names, sales
quantity and prices for each film (and you can have these too, if you run
the following script):
CREATE
TABLE #Store1
(Sales smallint, Film_Title varchar(100),
Price Decimal(4,2))
Insert
into #Store1
Values (2,
'Wonder Woman', 4.99), (1, 'Casino Royale',
2.99), (5, 'Wasabi', 7.99), (0,
'Pulp Fiction', 10.99)
CREATE
TABLE #Store2
(Sales smallint, Film_Title varchar(100),
Price Decimal(4,2))
Insert
into
#Store2
Values (3,
'Thor: Ragnarock', 14.99), (4, 'Casino Royale',
2.99), (2, 'Dr Who', 7.99), (0,
'Paw Patrol', 20.99), (3, 'Wasabi', 7.99)
To consolidate these tables I can use MERGE - this is similar
to creating a join between the tables but has the scope to do more:
MERGE #store1 AS S1
USING #Store2 AS S2
ON S1.Film_Title = S2.Film_Title
In conjunction with MERGE we can use the following commands:
WHEN MATCHED
THEN
WHEN
NOT MATCHED BY SOURCE
THEN
WHEN
NOT MATCHED BY TARGET
THEN
WHEN MATCHED
WHEN MATCHED will let me do something when two rows in the
tables overlap (like an inner join). An example might include updating the Sales
column in #Store1 to include sales made in #Store2:
MERGE #store1 AS S1
USING #Store2 AS S2
ON S1.Film_Title = S2.Film_Title
WHEN MATCHED
THEN
UPDATE
SET S1.Sales = s1.sales +
s2.sales;
Now any film that appears in both tables will have their Sales
combined in the #Store1 Sales
column:
Store1 had sold 5
copies of Wasabi whilst Store2
sold only
3 copies. The total sales are
now listed in the
Store1 table as 8.
WHEN NOT MATCHED BY SOURCE
Sadly Pulp Fiction must be a dying cult classic, and nobody wants to watch it.
How could I get rid of it? First some new terminology:
The MERGE table (Store1) is referred to as the
TARGET whilst the USING table is the SOURCE
(Store2).
Pulp Fiction only exists in the TARGET (Store1) meaning
there is no corresponding value in the SOURCE table. In code
terms you can write:
MERGE #store1 AS S1
USING #Store2 AS S2
ON S1.Film_Title = S2.Film_Title
WHEN MATCHED
THEN
UPDATE
SET S1.Sales = s1.sales +
s2.sales
WHEN NOT
MATCHED
BY SOURCE
AND
S1.SALES = 0
THEN
DELETE;
The AND can be followed by a condition and outcome, such as
to
DELETE any unmatched rows that have a SALES
value of 0. This means Store1 now contains:
What? What happened to Pulp Fiction? Say what
again......I dare you.
The opposite would be copying any films not found in the TARGET
from the SOURCE table.
WHEN NOT MATCHED BY TARGET
The SOURCE table (Store2) has 3 films not
found in my TARGET table (Store1). I want to
copy these films if they have sold any copies :
MERGE #store1 AS S1
USING #Store2 AS S2
ON S1.Film_Title = S2.Film_Title
WHEN MATCHED
THEN
UPDATE
SET S1.Sales = s1.sales +
s2.sales
WHEN
NOT MATCHED BY SOURCE
AND
S1.SALES = 0 THEN
DELETE
WHEN NOT
MATCHED
BY TARGET
AND
s2.sales > 0
then
insert (Sales,
Film_Title, Price)
values(s2.sales,
s2.Film_Title, s2.price)
;
This means that if any films sold in Store2 weren't sold in
Store1 they have been added to the available stock:
We have removed any films that haven't sold (and included any
that have) from both stores into one combined table.
My stock is looking consolidated, but what changes did I make? When using
MERGE it is possible to capture all the changes made and present then
in a table.
OUTPUT
The OUTPUT clause gives us access to the INSERTED
and DELETED tables, and using the $action class
we can filter/show the class type:
MERGE #store1 AS S1
USING #Store2 AS S2
ON S1.Film_Title = S2.Film_Title
WHEN MATCHED
THEN
UPDATE
SET S1.Sales = s1.sales +
s2.sales
WHEN
NOT MATCHED BY SOURCE
AND
S1.SALES = 0 THEN
DELETE
WHEN
NOT MATCHED BY TARGET
AND
s2.sales > 0 then
insert (Sales, Film_Title, Price)
values(s2.sales, s2.Film_Title,
s2.price)
OUTPUT
$action,
inserted.*, deleted.*;
This gives a list of all columns affected by INSERT,
DELETE or UPDATE:
Whilst it isn't necessary to include $action,
it would make life harder working out what happened if you
omitted it!
The first set of column names are from the INSERTED table and
the second set are from the DELETED table. All film records I
changed are now in one place!
Try selecting specific columns from the INSERTED table
instead of using *. Also try OUTPUT INTO to
store data into a table variable.
