Tag Archives: 70-433

I spent a decent chunk of this afternoon studying for the upcoming 70-433 exam (rescheduled for December 20th). Today I focused primarily on MERGE and OUTPUT clauses.

MERGE is pretty interesting – compare the contents of two tables and update one based on the content in another. MERGE is new to SQL Server in 2008. OUTPUT is also interesting and I wish I’d known about it previously as it would’ve been very handy. OUTPUT let’s you see the changes you made and (optionally) store them! OUTPUT has been around in SQL Server since 2005.

I went through a tutorial on MERGE and then read up on both MERGE and OUTPUT on Books Online. Then, I closed by browser and tested myself to see what I’d learned.

Here’s what I came up with.

/* I'm opening a store that sells only things I like */

CREATE SCHEMA store
GO
CREATE TABLE store.products
( product_id INT
, product_name VARCHAR(150)
, product_desc VARCHAR(400)
, product_price MONEY)
GO
INSERT INTO store.products VALUES
(1, 'Bacon', 'Tasty Bacon', 5.49)
, (2, 'Cheese', 'Sharp Cheddar', 4.79)
, (3, 'Beer', 'Sierra Nevada Pale Ale', 8.49)
GO
SELECT * FROM store.products
GO

/* We're making some changes to our products and have another table with updates to our products list. */

CREATE TABLE store.product_updates
( product_id INT
, product_name VARCHAR(150)
, product_desc VARCHAR(400)
, product_price MONEY)
GO
INSERT INTO store.product_updates VALUES
(1, 'Bacon (Original)', 'Tasty Bacon, original recipe', 5.49)
, (3, 'Beer - IPA', 'Sierra Nevada Pale Ale', 8.49)
, (4, 'Bacon - Peppered', 'Tasty Pepper Bacon (new!)', 5.79)
, (5, 'Beer - Cheap', 'Yuengling Lager', 4.99)
GO
SELECT * FROM store.product_updates;
SELECT * FROM store.products;
GO

/* Use a Merge Statement to update our products based on the information in the product_updates table
NOTE: Not all products are listed in our source table, only products that have been changed */

MERGE store.products AS TARGET
USING store.product_updates AS SOURCE
ON (TARGET.product_id = SOURCE.product_id)
WHEN MATCHED AND
/* Update Product IDs that are matched*/
TARGET.product_name <> SOURCE.product_name
OR TARGET.product_desc <> SOURCE.product_desc
OR TARGET.product_price <> SOURCE.product_price THEN
UPDATE SET TARGET.product_name = SOURCE.product_name
, TARGET.product_desc = SOURCE.product_desc
, TARGET.product_price = SOURCE.product_price
/* WHEN NOT MATCHED BY SOURCE THEN
The product has no update, don't do anything
If we wanted to, we could delete rows that weren't found in the updates table */
WHEN NOT MATCHED BY TARGET THEN
/* This must be a new product, insert it! */
INSERT (product_id, product_name, product_desc, product_price)
VALUES (SOURCE.product_id, SOURCE.product_name, SOURCE.product_desc, SOURCE.product_price);
GO
SELECT * FROM store.products;
GO

Pretty sweet! Our products table is now updated with the appropriate changes!

Keeping a history of your changes could be very useful. In this case, we could use them to track all sorts of things – like how sales compared when prices changed, etc. In my daily life, it would be nice to have the changes recorded as a backup in case I needed to undo some particular change and didn’t realize it for a few hours or days. OUTPUT comes in handy here. It’s not limited to being used with MERGE either – it can be used with INSERT, UPDATE or DELETE. It can just display the changes or it can write them out to a table.

/* What if we wanted to keep a record of changes? Use an OUTPUT clause with the MERGE statement! */

/* Create a table to store the historical changes */
CREATE TABLE store.product_history
(change_date DATETIME2 --when change occurred
, change_type VARCHAR(100) --insert, update or delete
, old_product_id INT --beginning product_id
, old_product_name VARCHAR(150) --beginning name
, old_product_desc VARCHAR(400) --beginning description
, old_product_price MONEY --beginning price
, new_product_id INT --new product_id
, new_product_name VARCHAR(150) --new name
, new_product_desc VARCHAR(400) --new description
, new_product_price MONEY) --new price
GO

/* Same MERGE statement with an OUTPUT clause */

MERGE store.products AS TARGET
USING store.product_updates AS SOURCE
ON (TARGET.product_id = SOURCE.product_id)
WHEN MATCHED AND
/* Update Product IDs that are matched*/
TARGET.product_name <> SOURCE.product_name
OR TARGET.product_desc <> SOURCE.product_desc
OR TARGET.product_price <> SOURCE.product_price THEN
UPDATE SET TARGET.product_name = SOURCE.product_name
, TARGET.product_desc = SOURCE.product_desc
, TARGET.product_price = SOURCE.product_price
/* WHEN NOT MATCHED BY SOURCE THEN
The product has no update, don't do anything
If we wanted to, we could delete rows that weren't found in the updates table */
WHEN NOT MATCHED BY TARGET THEN
/* This must be a new product, insert it! */
INSERT (product_id, product_name, product_desc, product_price)
VALUES (SOURCE.product_id, SOURCE.product_name, SOURCE.product_desc, SOURCE.product_price)
OUTPUT
GETDATE() --date of change
, $action --type of change
, DELETED.* --old stuff
, INSERTED.* --new stuff
INTO store.product_history;
GO
SELECT * FROM store.product_history;

On a side note, I found that Oracle has a command similar to MERGE. Since it’s a combination of an UPDATE and a DELETE, they called it UPSERT! That sounds way more fun than MERGE.