So far, we’ve talked about the following:
Reading Data Sources – SELECT in SQL
Inserting into Data Sources – INSERT in SQL
Deleting from Data Sources – DELETE in SQL
Updating Data Sources – UPDATE in SQL
Now, we’re going to combine everything by merging data sources. To make this easier, we’ll break this down into a few examples.
First, we’ll take the easiest and most common example – Inserting rows that don’t exist and updating those that do.
1 MERGE INTO [SampleTable] AS Target
2 USING VALUES (‘Wilkie’, ‘Kevin’, ‘R’), (‘Smith’, ‘Jill’, ‘A’), (‘Thompson’, ‘Hunter’, ‘S’) AS Source (Last, First, Middle)
3 ON Target.LastName = Source.Last AND Target.FirstName = Source.First
4 WHEN Matched THEN
5 UPDATE SET MiddleInitial = Source.Middle
6 WHEN NOT MATCHED BY Target THEN
7 INSERT (LastName, FirstName, MiddleInitial) VALUES (Last, First, Middle)
Since there are a lot of things going on, let’s look at each line individually. I even added a line number, just to make things as simple as possible.
Line 1 – We are simply merging everything into everyone’s favorite table [SampleTable], which we will henceforth call, Target.
Line 2 – Since there’s only a few items we want to do anything with, we’re defining the values in a new table that we’ll call Source and give it the column names of Last , First , and Middle.
Line 3 – The columns of First and Last in the Source table will be compared against FirstName and LastName in the Target table to see what needs to be done to the Target table.
Line 4 – What should the system do if, for example, there is a row with everyone’s favorite blogger, moi, in both the Target and Source table.
Line 5 – Simply update the MiddleInitial column of the matches. Please note that since it matches, you don’t have to write it like the normal UPDATE statement – meaning no WHERE clause.
Line 6 – What should we do if a row is in the Source table, but not in the Target table.
Line 7 – A Simple INSERT of the values that were not in the Target table according to the criteria specified in Line 6.
Now, that wasn’t too bad, was it? Yes, it’s a little complicated at first, but it uses a lot of the items we’ve learned recently. So next time, we’ll step through another example of a usage of the MERGE statement. So until then…
1 comment