Introduced in SQL 2008 the merge function is a useful way of inserting, updating and deleting data inside one SQL statement.
In the example below I have 2 tables one containing historical data using type 2 SCD (Slowly changing dimensions) called DimBrand and another containing just the latest dimension data called LatestDimBrand. Using the merge function I will insert new records from LatestDimBrand into DimBrand, I will archive (apply an end date) to any DimBrand records which do not appear in the latest data, and finally enter a new record and archive the old record for any Brands which may have changed.
DimBrand (the target of our Inserts, Updates, Deletes) and DimLatestBrand (the source for Inserts,Updates,Deletes):
The merge code in it’s entirety:
INSERT #DimBrand ([BrandCode],[BrandName],[StartDate]) SELECT [BrandCode],[BrandName],getdate() FROM ( MERGE #DimBrand AS Target USING ( SELECT [BrandCode],[BrandName],[StartDate],[EndDate] FROM #LatestDimBrand ) AS Source ON (Target.[BrandCode] = Source.[BrandCode]) ------------------------------- WHEN MATCHED AND Target.[BrandName] <> Source.[BrandName] THEN UPDATE SET Target.[EndDate] = getdate() ------------------------------- WHEN NOT MATCHED BY TARGET THEN INSERT ( [BrandCode] ,[BrandName] ,[StartDate] ,[EndDate] ) VALUES ( Source.[BrandCode], Source.[BrandName], Source.[StartDate], Source.[EndDate] ) ------------------------------- WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Target.[EndDate] = getdate() ------------------------------- OUTPUT $Action, Source.* ) As i([Action],[BrandCode],[BrandName],[StartDate],[EndDate]) ------------------------------- WHERE [Action] = 'UPDATE' AND BrandCode IS NOT NULL
The insert statement, although appearing at the top of the SQL statement is the last thing to be executed and uses the results set of the merge function specified in the OUTPUT clause further down.
Next is the start of our merge statement here we specify a Target and a Source table. We also specify which columns the 2 tables should match on, in this case only the BrandCode.
The next step is to specify and handle any matches, here we are looking for Brand Code’s which appear in both tables, but with differing Brand Names. If a match is found the row in the target table is given an end date. As with the following “NOT MATCHED” section inserts,updates or deletes can be applied to either table here. This handles the first part of our Slowly Changing Dimension requirement.
Where there is no match between the Target table and the Source table, the relevant records from the Source table are inserted into the Target table.
The penultimate part of the merge statement takes any Target records which do not appear in our “Latest” Source table and sets an EndDate for them as they are no longer a valid record.
The OUTPUT clause populates the outer INSERT statement, here the Source rows which are involved in the UPDATE statements above are pulled out to the outer FROM clause. We have also selected $Action so that we can filter out the INSERTED rows under the NOT MATCHED by Target statement.
The result of all the above is the brand “Googles” is archived along with the “MikeRowSoft” which as you can see from the BrandCode has been rebranded as MickRowSoft.
Meet the Team – Catherine Sachdev, Marketing Assistant
Next up we’re introducing you to Catherine Sachdev. Catherine joined us just over a year
Jan
Data Lineage with Azure Purview
I wrote an introductory Purview blog post previously, where I explored what the tool is
Jan
The Next Era of Retail: How Technology is driving change in a COVID-19 World
The retail sector is of great importance and accounts for almost 5% of GDP and
Jan
Meet the Team – Alex Kordbacheh, Junior Consultant
It’s time for another Meet the Team blog! This time we’re introducing you to Alex
Dec
Use cases for Recursive CTEs
Introductions Recursive CTEs are a way to reference a query over and over again, until
Dec
Azure Sentinel is named a ‘Leader’ in the Forrester Research Wave Report
Microsoft have recently announced that they have been named a Leader by Forrester Research in
Dec
Getting Started with Azure Purview
Azure Purview (a.k.a Data Catalog gen 2) has been released into preview and is currently
2 Comments
Dec
An Introduction to ApexSQL Complete – Integration with SSMS
We all know that the idea of add-ins is to make our lives easier. In
Dec