This statement can significantly reduce the code needed to perform validations and insert data from source to destination, or updating it if already exists, even you can choose to delete data instead.
The general form is:
Let us watch an example of use, using the AdventureWorks database, I have created a test table with fewer records from the original table using simple select into:
We can see the difference in rows:
|Out test target table|
Also, let us change the modified date, just to show you how the update works:
We can see the difference on records and dates:
|In our test table, only records with the word 'Dollar' exists|
Now, using the MERGE sentence, we update existing records with one date, and add the missing records with other date:
Now take a look at the row count, now both tables match:
|same number of records :)|
And the data:
|Sometimes is Christmas, sometimes is Halloween|
Please note that the ON sentence is used as a compare method to know if the row matches or not, in most cases you will use your table primary key to perform the matching.
You can use WHEN MATCHED more than once if you use it with an AND argument.