Merging Data Sources

So far, we’ve talked about the following:

Reading Data SourcesSELECT in SQL

Inserting into Data SourcesINSERT in SQL

Deleting from Data SourcesDELETE in SQL

Updating Data SourcesUPDATE 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
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…

One thought on “Merging Data Sources

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s