Normally when you have rows from one table you want to move over into the other, you'll have to run both an update query to get your existing rows updated with the new values and insert query to get new rows over to your target. That means you have to write two queries. With the merge, you only need one as it performs both the update and the insert.
So let's take a look at it.
First, I'll create 2 tables with dummy data; a tblSource and a tblTarget.
The scripts for creating the tables and data can be found here merge_setup_20100521.sql (3.58 kb), but basically it's just a target and source table with an ID, ValA, ValB and ValC with a clustered index on ID and some dummy data.
So to do the update/insert it would look like this:
UPDATE MyTarget SET
MyTarget.ValA = MySource.ValA,
MyTarget.ValB = MySource.ValB,
MyTarget.ValC = MySource.ValC
FROM dbo.tblTargetInUp AS MyTarget
INNER JOIN dbo.tblSourceInUp AS MySource ON MyTarget.ID = MySource.ID
INSERT INTO dbo.tblTargetInUp
SELECT ID, ValA, ValB, ValC
FROM dbo.tblSourceInUp AS MySource
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblTargetInUp AS MyTarget WHERE MyTarget.ID = MySource.ID)
If we look at the execution plan, I get an estimated cost of 9.1754 for the update and 2.48725 for the insert, meaning a combined cost of 11.66265 for 33.333 rows updated and 33.3334 rows inserted, into a target of 66.666 rows.
Now do remember, the estimated cost is just a number for how the query runs in my environment, it can't be taken as a direct number and transferred to another system - I'm only interested in the relative comparison with the merge. More data, more indexes, more variations will all affect the actual numbers. Also if looking at the execution plan, it is clear it is two queries we fire, meaning that all the overhead which goes into running one query will be doubled for this. But it was how you'd have to do merges in the past.
Now, with the merge syntax we can do it like this:
MERGE dbo.TblTargetMerge AS MyTarget USING
(
SELECT * FROM dbo.TblSourceMerge AS TS
) AS MySource
ON MyTarget.ID = MySource.ID
WHEN MATCHED THEN
UPDATE SET
MyTarget.ValA = MySource.ValA,
MyTarget.ValB = MySource.ValB,
MyTarget.ValC = MySource.ValC
WHEN NOT MATCHED THEN
INSERT
(ID, ValA, ValB, ValC)
VALUES
(MySource.ID, MySource.ValA, MySource.ValB, MySource.ValC)
;
Note that the syntax takes both the update and insert in the WHEN MATCHED and WHEN NOT MATCHED.
For the complete overview over the syntax, I'll refer you to the documentation by microsoft: MERGE (Transact-SQL).
But basically – you MERGE into a table using a source, and then define the ON clause (as you would a join), and then specify the WHEN MATCHED and the WHEN NOT MATCHED clauses.
One thing I'll expand on myself though, is the OUTPUT clause which can also be coupled on to the merge. I mentioned the OUTPUT clause myself recently.
The important thing is that you can couple the $action to the output clause and get information about whether you merged the data or you inserted the data, meaning whether the row was matched or not matched.
Like this:
MERGE dbo.TblTargetMerge AS MyTarget USING
<...snipped FOR being brief...>
OUTPUT $ACTION, rest_of_select_here
;
Nifty.
Anyways - once we've build this query, we can look at the execution plan, and here we clearly see it is handled as one query.
And in my case, the estimated cost is 8.407 for the same number of rows as above, meaning we’ve saved about 27.9% just by changing syntax.
Now, this structure I used to compare is very simple - with a simple matching of ID to ID and then just insert/overwrite everything so the actual result may vary (naturally), however the merge syntax does appear to be faster and with the added bonus of keeping the query combined into one syntax rather than divided into two different queries.
I must admit, I do like the merge syntax myself once I learned to read and write them.