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.
- 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.
- 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.
- 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 IF…ELSE 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.
Justin Cooney 
1 thought on “SQL Server Upserts – Using MERGE to INSERT or UPDATE Table Rows”