Programmer's Edge

Following up on my previous post a couple of people have asked me how they could accomplish this same thing because MERGE is not available in SQL Server 2005. So let’s take a look at how you can use the OUTPUT statement which is available to produce sort of the same affect.

So first we will set up our test data once again from the AdventureWorks database. This will act as the data that is coming into our target table

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

Then we’ll put some data into the target table…

  SELECT TOP 10 *
  INTO [AdventureWorks].[Person].[Contact_Base]
  FROM [AdventureWorks].[Person].[Contact_Temp] 

We change the email address to ensure that our T-SQL will in fact update what we are wanting to update

  UPDATE [AdventureWorks].[Person].[Contact_Base]
  SET [EmailAddress]='arie.jones@programmersedge.com'

Now we’ll take a look at the data to make sure it’s what we are expecting…..

So now we are going to write out statement that will mimic the MERGE stuff we did in the previous post. To clarify, we basically want to update the target table and if a particular ContactID exists then we want to update the EmailAddress column. If no such ContactID exists then we would like to insert the row.

Now OUTPUT statement works a lot like an inline TRIGGER in that you have access to an Inserted table. So you have access to the data that is actually being updated/inserted/whatever by the particular statement. So it’s not only good for this instance but also for other scenarios like logging. So we will first go and create as simple table variable to hold the ContactID values that we are going to update with our initial statement. Then we will be using the OUTPUT statement to fill up the table in conjunction with our UPDATE and lastly running an INSERT statement. Kind of a long way around the track when you look at the MERGE statement in the previous post…but hey ya’ gotta work with the tools you got.

 SET IDENTITY_INSERT [AdventureWorks].[Person].[Contact_Base] ON;  

 DECLARE @tblContacts table (ContId int); 

 UPDATE [AdventureWorks].[Person].[Contact_Base]
 SET EmailAddress=b.EmailAddress
 OUTPUT inserted.ContactID INTO  @tblContacts
 FROM [AdventureWorks].[Person].[Contact_Base] a INNER JOIN
 [AdventureWorks].[Person].[Contact_Temp] b ON a.ContactID=b.ContactID
;

INSERT INTO [AdventureWorks].[Person].[Contact_Base]
([ContactID],[Title],[FirstName],[MiddleName]
  ,[LastName],[EmailAddress])
SELECT [ContactID],[Title],[FirstName]
      ,[MiddleName],[LastName],[EmailAddress]
      FROM [AdventureWorks].[Person].[Contact_Temp]
WHERE ContactID NOT IN (SELECT ContId from @tblContacts);

Now when we look at the table with a simple SELECT statement we see that everything is as it should be….

SUCCESS! Hopefully, this helps someone else that may be in a jam because of using an older version of SQL Server ….

Cheers,

AJ

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