SQL Server Upserts – Using MERGE to INSERT or UPDATE Table Rows

Microsof SQL Server

Microsof SQL Server

The idea of an Upsert was added in the SQL 2003 standard for databases and is implemented using a MERGE statement in both Oracle and SQL Server.

So You Might Ask: What is an Upsert?

An Upsert does pretty much does what it sounds like: it runs an update on a row where a condition exists and it runs an insert if the condition does not exist.

What are the Benefits of Using a MERGE Upsert

If you look at the difference between a conditional INSERT…UPDATE code snippet and a MERGE statement you will see that the MERGE statement does have its benefits.

  1. For starters, the MERGE statement is less verbose and encapsulates the entire logic of the Insert/Update into a single statement. This is beneficial for readability and maintainability.
  2. Also the MERGE statement is more performant since SQL Server needs to deal with one query rather than a several separate queries and a temporary variable.
  3. The MERGE statement runs as a single atomic statement and so you don’t need to manually encapsulate it in a transaction like you would with the conditional version.

Upserting in SQL Server

Since I like to code for SQL Server I thought I’d try out using the MERGE statement as an Upsert. In doing so it’s important to note that Microsoft added support for the MERGE statement as of SQL Server 2008, so the MERGE statement will not work on older versions of SQL Server.

As an aside, the MERGE statement can be used for more than Upserting. But in this article I will specifically focus in this article about how to use the MERGE statement as an UPSERT to add or update information in a single table.

If you find this article useful and would like to read more about SQL Server and T-SQL, feel free to check out other SQL Tips and Tricks I have written.

Updating or Inserting Rows with a Conditional Statement

Traditionally (pre SQL Server 2008) Upserts have been coded using conditional statements that encapsulate UPDATE or INSERT actions.

Here is an example of using an IFELSE statement to do either an UPDATE or an INSERT depending on if particular data was found in a table:

DECLARE @TblTest AS TABLE(
 ID INT IDENTITY,
 ValueToTest VARCHAR(50)
 )
INSERT INTO @TblTest (ValueToTest) VALUES ('First Entry')
 INSERT INTO @TblTest (ValueToTest) VALUES ('Second Entry')
 INSERT INTO @TblTest (ValueToTest) VALUES ('Third Entry')
SELECT * FROM @TblTest
DECLARE @CheckForThisID INT
SELECT TOP 1 @CheckForThisID=ID FROM @TblTest WHERE ValueToTest='Second Entry'
IF @CheckForThisID IS NULL
 BEGIN
 INSERT INTO @TblTest (ValueToTest) VALUES ('Inserted value')
 END
 ELSE
 BEGIN
 UPDATE @TblTest SET ValueToTest = 'Updated value' WHERE ID = @CheckForThisID
 END
SELECT * FROM @TblTest

As you can see from the example code above, the conditional method of Updating or Inserting is fairly verbose and involves a series of statements.

Updating or Inserting Rows with a MERGE Statement

Thankfully the MERGE statement has come to the rescue and allows us to write the same logic in a single statement as follows:

DECLARE @TblTest AS TABLE(
 ID INT IDENTITY,
 ValueToTest VARCHAR(50)
 )
INSERT INTO @TblTest (ValueToTest) VALUES ('First Entry')
 INSERT INTO @TblTest (ValueToTest) VALUES ('Second Entry')
 INSERT INTO @TblTest (ValueToTest) VALUES ('Third Entry')
SELECT * FROM @TblTest
MERGE INTO @TblTest [TestingTable]
 USING
 (SELECT 'Second Entry' [SearchZone]) AS [SourceInfo]([SZone])
 ON [TestingTable].ValueToTest = [SourceInfo].[SZone]
 WHEN MATCHED THEN
 UPDATE SET ValueToTest = 'Updated value'
 WHEN NOT MATCHED THEN
 INSERT (ValueToTest) VALUES ('Inserted value');
SELECT * FROM @TblTest

Other Uses

In addition to supporting INSERT and UPDATE, the MERGE statement in SQL Server can be used to DELETE records. This tends to be more useful when working with two tables in the MERGE statement and does not hold as much value for an UPSERT to a single table.

Resources:

About these ads

One thought on “SQL Server Upserts – Using MERGE to INSERT or UPDATE Table Rows

  1. Pingback: SQL Server Upserts - Using MERGE to INSERT or U...

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s