T-SQL Basics: MERGE Statement

One useful sentence when you need to add data to an existing table from an external source or database, and you are not sure if some of the data exists or not, is the MERGE statement.

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:

MERGE INTO  <target_table> 
USING <table_source>   
    ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ]  
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] [ ...n ];

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:

SELECT *
INTO [AdventureWorks2014].[Sales].[Currencytest]
FROM [AdventureWorks2014].[Sales].[Currency]
WHERE name like '%Dollar%' 

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:

UPDATE [AdventureWorks2014].[Sales].[Currencytest]
SET ModifiedDate = 'jan 01 2017' 

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:

MERGE INTO [AdventureWorks2014].[Sales].[Currencytest] ct -- our test table
USING [AdventureWorks2014].[Sales].[Currency] c --source table
ON ct.CurrencyCode = c.CurrencyCode  
WHEN MATCHED   
    THEN UPDATE SET
 ct.name = c.name,
 ct.ModifiedDate = 'Oct 31, 2017' --the update date is Halloween
WHEN NOT MATCHED 
    THEN INSERT 
 VALUES(c.CurrencyCode,c.Name, 'Dec 25 2017') --insert date is Christmas :)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE; --if you have data in the destination you want to delete it

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.


Source:

https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql