How many times have you built logic in your code that first checks for the existence of a row, then based on the outcome of that existence check you either perform an INSERT statement (if the row doesn’t exist), or an UPDATE statement (if the row already exists)? With the release of SQL Server 2008 Microsoft introduced the MERGE statement to make that task a lot easier. In this article I’ll be discussing how to perform insert, update and delete logic using the MERGE statement. This is also the last article in this stairway series.

What are the Capabilities of the MERGE statement?

The MERGE statement allows you to write code to determine how to compare two tables and then determine if an INSERT, UPDATE, and/or DELETE operations needs to be performed against one of the tables. The MERGE statement can also use the TOP clause to only perform a specific number of operations. Like the normal INSERT, UPDATE and DELETE statements the MERGE statement can also output information so you can identify specifically what rows and values where inserted, updated, or deleted. The best way to describe the capabilities of the MERGE statement is with a series of examples.

Simple INSERT or UPDATE Logic

In this example I will be using the MERGE statement to INSERT or UPDATE records in one table based on the records contained in another table. This type of processing is commonly called an “UPSERT” (UPDATE/INSERT) operation. These tables are identified to the MERGE statement as a “Source” and a “Target” table. The “Target” is the table that will have records updated or inserted, whereas the “Source” table contains the records that will be used by the MERGE statement to identify the records in the “Target” table that will be updated or inserted.

For this example I need to first create two different sample tables. If you want to follow along and run my example you can use the code in Listing 1 to create my two sample tables.

USE tempdb;
GO
SET NOCOUNT ON;
-- Create Target Table 
CREATE TABLE dbo.Sales (Id int,  
                    SalesAmount money);
INSERT INTO dbo.Sales VALUES(1,10.99);
-- Create Source Table  
CREATE TABLE dbo.NewSalesNAdjustments(Id int, 
                                      SalesAmount money);
INSERT INTO dbo.NewSalesNAdjustments VALUES (1, 12.99);
INSERT INTO dbo.NewSalesNAdjustments VALUES (2, 5.99);

Listing 1: Create sample target and source tables

In Listing 1 I created the table dbo.Sales. This table will be the “Target” table for my MERGE operation. The second table created is my “Source” table that is called dbo.NewSalesNAdjustments. This table will be used to update/insert rows in the dbo.Sales table using the MERGE statement.

If you review the records in the dbo.NewSalesNAdjustments and compare them to the records in dbo.Sales table, based on the Id column, you can see the row with an Id value of 1 is in both tables. In my MERGE example, I will use the Id column to determine whether or not a row from the dbo.NewSalesNAdjustments should update or insert a row in the dbo.Sales table. I want my merge logic to update the “Target” table anytime there are matching rows between the “Source” and “Target” tables based on the Id column values. But if any rows in the “Source” table do not have matching rows in the “Target” table based on Id column, then an INSERT operation will be will be performed. My MERGE statement in Listing 2 will accomplish this “UPSERT” operation.

USE tempdb;
GO
-- Update and Insert into Customers
MERGE dbo.Sales AS T -- Target
USING dbo.NewSalesNAdjustments AS S -- Source
ON T.Id = S.Id
WHEN MATCHED THEN -- Update
  UPDATE SET T.SalesAmount = S.SalesAmount
WHEN NOT MATCHED THEN -- Insert
  INSERT (Id,SalesAmount) VALUES (S.Id,S.SalesAmount);
-- Verify UPSERT operation
SELECT * FROM dbo.Sales;

Listing 2: MERGE statement performing an Upsert operation

In my MERGE statement in Listing 2 the “Target” table is the table that is referenced right after the MERGE keyword. The “Source” table of my MERGE statement is reference right after the USING keyword.

In order to identify the records to be inserted or updated the ON clause is used to join the records between the “Target” and “Source” tables, just like you would do in a normal JOIN operation. In my example I joined these two table based on the Id column, to determine if a record in the “Target” table should be updated or inserted the WHEN clauses are used. The two WHEN clauses in this example have two different criteria. The first one says “WHEN MATCHED”, and the second one says “WHEN NOT MATCHED”. The “WHEN MATCHED” option the action to perform when a record in the “Source” table matches a record in the “Target” table based on the ON condition. If the “WHEN MATCHED” criteria is met then the code will use column information in the matched “Source” table record to update the matching record in the “Target” table. If a row in the “Source” table doesn’t match to a record in the “Target” table then the “WHEN NOT MATCHED” logic will be used to insert a record into the “Target” table, based on the column information in the “Source” table for the unmatched row.

The SELECT statement at the bottom of my code in Listing 2 displays the rows in my “Target” table after the MERGE statement has executed. The output displayed by this SELECT statement can be found in Report 1.

Id          SalesAmount
----------- ---------------------
1           12.99
2           5.99

Report 1: Resulting record set after MERGE statement in Listing 1 is executed

By reviewing the results in Report 1 you can see that the SalesAmount for the row with an Id of 1 was updated from 10.99 to 12.99. This was accomplished because an Id column value of 1 met the WHEN MATCHED condition, which caused the UPDATE statement to be executed that used the SalesAmount column value from the “Source” table to update the “Target” table. The row with an Id value of 2 was inserted into the “Target” table because the row with an Id value of 2 in the “Source” table met the NOT MATCHED condition.

Deleting Records Using the MERGE Statement

The MERGE statement can perform more than just an INSERT and UPDATE against the “Target” table, it can also perform DELETE operations. Suppose you need to build a process that needs to maintain a table that contains all the records in a source system. Meaning when a records are inserted, update, or deleted from the source system you want to insert, update, or delete records from the table you are maintaining.

To show you how this works I will be maintaining a CurrentListing table for a real estate brokerage that contains current properties that are for sale. Monthly, I will get a new list of current properties from another source that needs to be used to keep my CurrentListing table up to date. I will use the MERGE statement to keep my CurrentListing table updated. Prior to showing you my MERGE statement I need to create and populate my target CurrentListing table, as well as create a table that will contain the monthly current listings I get from my another source. I will be populating both of these tables by using the code in Listing 3.

USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE dbo.CurrentListing -- My Target Table
(ListingID int, 
ListingAddress varchar(250),
BedroomCount tinyint, 
BathCount decimal(2,1), 
Squarefootage smallint,
GarageSize tinyint,  
AskingPrice decimal(10,2));
INSERT dbo.CurrentListing VALUES (1,'1234 MyStreet, Somewhere, WA',4, 2, 2100,2,275000);
INSERT dbo.CurrentListing VALUES (2,'9876 X Street, Nowhere, WA',3, 1.5, 1500,0,175000);
INSERT dbo.CurrentListing VALUES (3,'9990 Highend Way, Richville, WA',8, 6, 8000,3,2500000);
CREATE TABLE dbo.MonthlyListingUpdate -- My Source Table 
(ListingID int, 
ListingAddress varchar(250),
BedroomCount tinyint, 
BathCount decimal(2,1), 
Squarefootage smallint,
GarageSize tinyint,  
AskingPrice decimal(10,2));
INSERT dbo.MonthlyListingUpdate VALUES (1,'1234 MyStreet, Somewhere, WA',4, 2, 2100,2,275000);
INSERT dbo.MonthlyListingUpdate VALUES (3,'9990 Highend Way, Richville, WA',8, 6, 8000,3,2000000);
INSERT dbo.MonthlyListingUpdate VALUES (4,'1950 Grand Lane, Somewhere, WA',4,2,2350,2,325000);

Listing 3: Script to create Target and Source tables for Insert, Update, and Delete MERGE example

In Listing 3 I created two tables: CurrentListing, and MonthlyListingUpdate. The CurrentListing table will be the “Target” table of my MERGE statement. This is the table that needs to be update/maintained with current listings. The MonthlyListingUpdate table contains the records I get monthly, which represents a view of all the current listings. The records in the MonthlyListingUpdate table will be the “Source” records for my MERGE statement.

To maintain my CurrentListing table all I need to do is create the right MATCHED and NOT MATCHED conditions to determine when to do an INSERT or UPDATE, and then include a new NOT MATCHED BY SOURCE condition to perform the DELETE operation. I will use MERGE code in List 4 to keep my CurrentListing table up to date.

USE tempdb;
GO
MERGE dbo.CurrentListing AS T -- Target
USING dbo.MonthlyListingUpdate AS S -- Source
ON T.ListingId = S.ListingId
WHEN MATCHED THEN -- Update
  UPDATE SET T.ListingAddress = S.ListingAddress,
             T.BedroomCount = S.BedroomCount,
             T.BathCount = S.BathCount, 
             T.Squarefootage = S.Squarefootage,
             T.GarageSize = S.GarageSize,  
             T.AskingPrice =S.AskingPrice
WHEN NOT MATCHED THEN -- Insert
  INSERT (ListingId,
          ListingAddress, 
          BedroomCount,
          BathCount, 
          Squarefootage,
          GarageSize, 
          AskingPrice) 
  VALUES (S.ListingId,
          S.ListingAddress, 
          S.BedroomCount,
          S.BathCount, 
          S.Squarefootage,
          S.GarageSize, 
          S.AskingPrice) 
 WHEN NOT MATCHED BY SOURCE THEN -- DELETE
   DELETE;

Listing 4: Performing INSERT, UPDATE and DELETE statements using different MERGE criteria

The code in listing 4 is similar to the MERGE statement in Listing 2, except this time I also included a DELETE clause. To identify which records in the “Target” table are to be deleted I specified a “WHEN NOT MATCHED BY SOURCE” clause. This clause tells SQL Server whenever it finds a record in the “Target” table that is not contained in the "Source" table that it needs to perform a DELETE operation.

Using the OUTPUT Clause with the MERGE Statement

If you need to monitor what the MERGE statement is doing you can use the OUTPUT clause to display information about what values that were updated, inserted or deleted. To demonstrate how to use the OUTPUT clause review the code in listing 5.

USE tempdb;
GO
-- Stage MonthlyListingUpdate table 
-- For MERGE statement with OUTPUT clause
TRUNCATE TABLE dbo.MonthlyListingUpdate
INSERT dbo.MonthlyListingUpdate VALUES (3,'9990 Highend Way, Richville, WA',8, 6, 8000,3,2500000);
INSERT dbo.MonthlyListingUpdate VALUES (5,'9301 Bayview Ln, Nowhere, WA',4,2,2350,2,325000);             
-- MERGE Statement with OUTPUT  
MERGE dbo.CurrentListing AS T -- Target
USING dbo.MonthlyListingUpdate AS S -- Source
ON T.ListingId = S.ListingId
WHEN MATCHED THEN -- Update
  UPDATE SET T.ListingAddress = S.ListingAddress,
             T.BedroomCount = S.BedroomCount,
             T.BathCount = S.BathCount, 
             T.Squarefootage = S.Squarefootage,
             T.GarageSize = S.GarageSize,  
             T.AskingPrice =S.AskingPrice
WHEN NOT MATCHED THEN -- Insert
  INSERT (ListingId,
          ListingAddress, 
          BedroomCount,
          BathCount, 
          Squarefootage,
          GarageSize, 
          AskingPrice) 
  VALUES (S.ListingId,
          S.ListingAddress, 
          S.BedroomCount,
          S.BathCount, 
          S.Squarefootage,
          S.GarageSize, 
          S.AskingPrice) 
 WHEN NOT MATCHED BY SOURCE THEN -- DELETE
   DELETE
 OUTPUT $action, inserted.ListingId As insertedListingId,
                   deleted.ListingId as deletedListingId;

Listing 5: Using the OUTPUT clause

In the code in Listing 5 I ran the same MERGE statement as in Listing 4 with the addition of an OUTPUT clause at the end of the MERGE statement. Additionally, prior to the MERGE statement I cleaned up and repopulated my MonthlyListingUpdate table. This MERGE statement updated one row, inserted another, and deleted rows where the “Source”did not have a matching ListingID value in the“Target” table.

The OUTPUT clause that I added to Listing 5 uses the “inserted” and “deleted” pseudo tables to display the ListingId’s for those rows that where inserted or deleted, as well as displays whether an INSERT, UPDATE, DELETE statement was done by reference the $action notation. Note that I only displayed a single column value from the inserted and deleted pseudo tables, but I could have easily displayed all of the columns in those pseudo tables with the OUTPUT clause. When I run the code in Listing 5 I get the output in Report 2

$action    InsertedListingId DeletedListingId
---------- ----------------- ----------------
INSERT     5                 NULL
DELETE     NULL              1
UPDATE     3                 3
DELETE     NULL              4

Report 2: Results displayed by using OUTPUT clause on MERGE statement

By reviewing the output in Report 2 you can see that four different statements were executed. For each statement executed the ListingId values for the inserted and deleted pseudo tables were displayed. Note that the only the UPDATE statement contains a ListingID value in both the inserted and deleted pseudo tables. These two values represent the before and after images of the ListingId column when the UPDATE operation was performed.

TOP Clause

The MERGE statement also supports a TOP clause. Using the TOP clause on a MERGE statement is just like using the TOP clause on a SELECT statement. When you used the TOP clause, SQL Server will not more rows than the number supplied with the TOP clause. To demonstrate this I will run the code in Listing 6.

USE tempdb;
GO
SET NOCOUNT ON;
-- Stage Source table for TOP clause example 
TRUNCATE TABLE dbo.MonthlyListingUpdate
INSERT dbo.MonthlyListingUpdate VALUES (6,'1460 Vashon Street, Microtown, WA',8, 6, 8000,3,2500000);
INSERT dbo.MonthlyListingUpdate VALUES (7,'7651 Defiance Avenue, Softwareville, WA',4,2,2350,2,325000);             
INSERT dbo.MonthlyListingUpdate VALUES (8,'990 Shaking Way, SQLLand, WA',8, 6, 8000,3,2500000);
INSERT dbo.MonthlyListingUpdate VALUES (1,'1234 MyStreet, Somewhere, WA',4, 2, 2100,2,200000);             
-- MERGE Statement with TOP clause  
MERGE TOP(2) dbo.CurrentListing AS T -- Target
USING dbo.MonthlyListingUpdate AS S -- Source1
ON T.ListingId = S.ListingId
WHEN MATCHED THEN -- Update
  UPDATE SET T.ListingAddress = S.ListingAddress,
             T.BedroomCount = S.BedroomCount,
             T.BathCount = S.BathCount, 
             T.Squarefootage = S.Squarefootage,
             T.GarageSize = S.GarageSize,  
             T.AskingPrice =S.AskingPrice
WHEN NOT MATCHED THEN -- Insert
  INSERT (ListingId,
          ListingAddress, 
          BedroomCount,
          BathCount, 
          Squarefootage,
          GarageSize, 
          AskingPrice) 
  VALUES (S.ListingId,
          S.ListingAddress, 
          S.BedroomCount,
          S.BathCount, 
          S.Squarefootage,
          S.GarageSize, 
          S.AskingPrice) 
 WHEN NOT MATCHED BY SOURCE THEN -- DELETE
    DELETE
 OUTPUT $action, inserted.ListingId As insertedListingId,
                   deleted.ListingId as deletedListingId;

Listing 6: Script to create Sample Data

By reviewing the code in Listing 6 you can see that the “Source” table for my MERGE statement contains 3 new listings and one updated listing. Additionally, all of my existing rows in the CurrentListing table are missing from the “Source” table. When I run the code in Listing 6 I get the output in Report 3.

$action    insertedListingId deletedListingId
---------- ----------------- ----------------
INSERT     6                 NULL
INSERT     7                 NULL

Report 3: Results displayed by using OUTPUT clause on MERGE statement

As you can see, my MERGE statement with the TOP clause only executed two INSERT statements for records with ListingID values of 6 and 7. The TOP clause caused my MERGE statement to not perform the additional INSERT, UPDATE, or DELETE operations that would have normally been executed if the TOP clause was not present.

A MERGE Primer

The examples here are only meant to be a primer to help you understand the MERGE statement. Keep in mind there are many different ways you can put together the MATCH. NOT MATCHED, and NOT MATCHED BY SOURCE conditions to help you build the insert, update and delete logic needed to help you maintain a SQL Server table.

As already stated this is the final article in the “Stairway to T-SQL DML” series. This stairway exposed you to the history of SQL Server and provided you with the basic DML statement to allow you to query and maintain your SQL Server database tables.

What’s Next?

The next Transact-SQL Stairway will cover more advanced topics. I will be discussing additional T-SQL functionality that will allow you round out your T-SQL programming skills. Here is a partial list of topics that will I be covering:

• CASE statement
• WHILE loop
• IF THEN ELSE logic
• Sub-Queries
• User Defined Functions
• Stored Procedures
• Triggers
• Use of Temporary Table
• Common Table Expressions (CTEs).