![]() |
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%'
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'
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
Comments
Post a Comment