Merging Data Sources Part II

The last time we were together, we learned how to use the MERGE statement when we wanted to insert rows that didn’t exist and update rows that didn’t. This time we’re going to add onto that. We’re adding the seldom used, but delightfully potent – delete rows that no longer exist in the original table.

So, most of it is the exact same coding for our example:

MERGE INTO [SampleTable] AS Target
USING VALUES (‘Wilkie’, ‘Kevin’, ‘R’), (‘Smith’, ‘Jill’, ‘A’), (‘Thompson’, ‘Hunter’, ‘S’) AS Source (Last, First, Middle)
ON Target.LastName = Source.Last AND Target.FirstName = Source.First
WHEN Matched THEN
UPDATE SET MiddleInitial = Source.Middle
WHEN NOT MATCHED BY Target THEN
INSERT (LastName, FirstName, MiddleInitial) VALUES (Last, First, Middle)
WHEN NOT MATCHED BY Source THEN
DELETE

As you’ll notice, only the last 2 rows were added onto the code block.

Since the data does exist on the Target, but not on the Source table, this code would remove the row from the Target table.

Now, if you’ve been paying attention to other posts in our series, you know this should not be our normal practice. Remember that we, usually, don’t delete data from our data tables.

Now, the other big thing I want to make mention of, is to provide detail into exactly what happened during the SQL operation. To do that, we just add to the end of the SQL statement the following:

OUTPUT $action, Inserted.*, Deleted.*

This will give us something similar to the following –

$action Last First Middle Last First Middle
—————— —————— ——— ——————– ——— ——– —————-
DELETE NULL NULL NULL Wilkie Kevin NULL
DELETE NULL NULL NULL Reitz Frank F
INSERT Wilkie Kevin R NULL NULL NULL
INSERT Smith Jill A NULL NULL NULL
INSERT Thompson Hunter S NULL NULL NULL

In this example, we can tell from the result set, the following happened:

1. Jill Smith and Hunter S Thompson rows were inserted into the Target table.
2. Kevin Wilkie was updated in the Target table (There is 1 INSERT row and 1 DELETE row, this is how you know it’s an UPDATE since there is nothing that will say “UPDATE” as an $Action.)
3. Frank F Reitz was deleted from the Target table.

And with that, we have reached the end of our series. Next time, we’ll learn something awesome I promise!