SQL Server 2008 T-SQL: MERGE

In 2008 SQL Server finally provided the developer with a simple way in which to do what is commonly referred to as an ‘upsert’ with the MERGE statement. So the basis of the MERGE is that you have a base table that you want to perform updates on and a set of data that you would like to use in order to update it. If a particular row of data exists you would like to update it. If it is not then you would like to insert it. Pretty simple, except that in older versions of T-SQL(circa 2008) you would have to either mess around with doing singular updates/inserts or if you have 2005 you could use the OUTPUT statement.

So let’s set up a simple example using the AdventureWorks database. We’ll first create a table for our set of data from the Contacts table

SELECT TOP 1000 [ContactID] 
      ,[Title] 
      ,[FirstName] 
      ,[MiddleName] 
      ,[LastName] 
      ,[EmailAddress] 
  INTO [AdventureWorks].[Person].[Contact_Temp] 
  FROM [AdventureWorks].[Person].[Contact]

Now let’s make a table that will be our base table. This one will act as the one that we want to update with source data. Just to show that there is nothing hidden up our sleeve we will even go and change the email address just to be sure that things are updated appropriately.

  SELECT TOP 10 * 
  INTO [AdventureWorks].[Person].[Contact_Base]
  FROM [AdventureWorks].[Person].[Contact_Temp]
  
  
  UPDATE [AdventureWorks].[Person].[Contact_Base]
  SET [EmailAddress]='arie.jones@programmersedge.com'
  
  SELECT * FROM [AdventureWorks].[Person].[Contact_Base]

So now this is what our base table looks like…..

Now let’s try out the MERGE technique. We are simply going to look at the ContactID and update the EmailAddress appropriately as needed.

SET IDENTITY_INSERT [AdventureWorks].[Person].[Contact_Base] ON;
  
MERGE INTO [AdventureWorks].[Person].[Contact_Base] AS Target
USING (SELECT ContactID,Title,FirstName,MiddleName,LastName,EmailAddress
       FROM [AdventureWorks].[Person].[Contact_Temp])
       AS Source (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress)
ON Target.ContactID = Source.ContactID
WHEN MATCHED THEN
    UPDATE SET EmailAddress = Source.EmailAddress
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress) 
    VALUES (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress);
    
SELECT * FROM [AdventureWorks].[Person].[Contact_Base] 
ORDER BY ContactID

Now there is a couple of important things to note here. One the IDENTITY_INSERT is needed because SQL Server went ahead and copied my Identity column for me when I created the initial table. Next, there are basically two import pieces of data in the MERGE state. One being the Target table and the other being the Source table. It is important to note that the Source data doesn’t have to be a SELECT statement but could be raw data or even some parameters passed through a stored procedure. Next comes the ON statement which tells the criteria how a MATCH should be recognized. So in our statement you can see the following creates a match on ContactID.

ON Target.ContactID = Source.ContactID

Now we need to say what happens when a MATCH event occurs. That is handled by this line.

WHEN MATCHED THEN
    UPDATE SET EmailAddress = Source.EmailAddress

And of course our NOT MATCHED criteria

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress) 
    VALUES (ContactID,Title,FirstName,MiddleName,LastName,EmailAddress)

What’s even more interesting is that you could have compounded MATCHED statements and the first one that matches the criteria will be executed. So we could do something like …

WHEN MATCHED AND Source.FirstName LIKE 'C%' THEN

Pretty cool functionality if you ask me. Now let’s look at the what the data looks like after the statement is executed.

See, works like a charm.

Cheers!

AJ