SQL Server 2008 MERGE

MERGE is a new DML statement in SQL Server 2008. Microsoft have implemented the ISO SQL 2003 and 2007 standard MERGE statement (as seen in Oracle and DB2) and added some extensions of their own.

In a nutshell, MERGE allows you to perform simultaneous UPDATE, INSERT and/or DELETE operations on one table. There are new physical operators that combine these operations so that they can be performed in a single scan rather than multiple scans.

MERGE has loads of possible applications. For the first time you can assign the contents of one table or query to another in a single operation. The following example requires SQL Server 2008 CTP4. Given this schema and data:

CREATE TABLE a
 (keycol INT PRIMARY KEY,
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  col3 INT NOT NULL);

CREATE TABLE b
 (keycol INT PRIMARY KEY,
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  col3 INT NOT NULL);

INSERT INTO a VALUES (1,0,0,0),(2,0,0,0);
INSERT INTO b VALUES (1,1,1,1),(3,3,3,3);

The following MERGE will populate table a with the same data as table b:

MERGE INTO a
USING b
 ON a.keycol = b.keycol
WHEN MATCHED THEN
 UPDATE SET
  col1 = b.col1,
  col2 = b.col2,
  col3 = b.col3
WHEN NOT MATCHED THEN
 INSERT (keycol, col1, col2, col3)
 VALUES (b.keycol, b.col1, b.col2, b.col3)
WHEN SOURCE NOT MATCHED THEN
 DELETE;

In the relational world this is the operation known as Relational Assignment ie:

 a := b

Unfortunately the SQL syntax is less pretty and requires just a little more typing!

MERGE also makes a good "upsert" for application CRUD stored procedures, removing the need for constructs like:

IF NOT EXISTS ...
  INSERT ...

Here's an example I created today. It inserts a new Vendor if and only if the name doesn't already exist. Whether the name previously existed or not, it returns the IDENTITY value of the existing or newly inserted row.

CREATE PROC dbo.usp_VendorUpsert

(

     @pVendorID INT OUTPUT,

     @pVendorName VARCHAR(80)

)

AS

BEGIN

     SET NOCOUNT ON;

     MERGE dbo.Vendor t

     USING (SELECT @pVendorName

            ) p(VendorName)

     ON t.VendorName = @pVendorName

     WHEN NOT MATCHED THEN

     INSERT (VendorName)

     VALUES (@pVendorName)

     WHEN MATCHED THEN

     UPDATE SET @pVendorID = VendorID;

     SET @pVendorID = COALESCE(SCOPE_IDENTITY(),@pVendorID);

END

RETURN

It's amazing that it took nearly 20 years for the SQL standards committee to come up with MERGE. Perhaps the delay is a legacy of the decision to make INSERT, UPDATE and DELETE the basic data update operators. INSERT, UPDATE and DELETE can all be defined as different kinds of relational assignment - assignment being the most basic type of update possible. So arguably MERGE is the more primitive and fundamental data update operator that ought to have been around earlier rather than later.