SQL Server: MERGE (AKA UPSERT) =(INSERT / UPDATE / DELETE) – All In One

MERGE statement does INSERT/UPDATE/DELETE

For the longest time Oracle and SQL Server users have written code like this (pseudo code)

IF (Key Exists)
   UPDATE row for key
ELSE
   INSERT new row with key

This is great but this is such a common construct that everyone has also been long craving for built in support in the database for what people commonly call UPSERT (UPDATE if key exists else INSERT). Oracle listened and introduced the MERGE statement in Oracle 9i. SQL Server followed suit in SQL Server 2008.

It has been 10 years and the adoption of this statement is still sparse. If you have not used it yet, please do. With all the clauses, it is more powerful than you think it is, with ability to INSERT/UPDATE and yes, DELETE with various sub-clauses to help obtain additional data for informational purposes.

Example – Walkthrough

Let us see the MERGE statement in action with a SQL Server example

Create a simple table with an ID and Description column

--
--Create a simple table with ID and Description
--
IF  EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MergeDemo]')
AND type in (N'U'))
      DROP TABLE [dbo].[MergeDemo]
GO

CREATE TABLE [dbo].[MergeDemo]
(
      [ID] [int] NOT NULL,
      [Description] [nvarchar](50) NULL
      CONSTRAINT [MergeDemo_PK] PRIMARY KEY NONCLUSTERED
      (
            [ID] ASC
      )
) ON [PRIMARY]
GO

Let us seed the table with 10 rows

--
--Insert 10 rows into the table
--
BEGIN
      DECLARE @i INT = 0;

      WHILE @i < 11
      BEGIN
            INSERT INTO [dbo].[MergeDemo](ID, Description)
VALUES (@i, 'Value for ID is ' + LTRIM(STR(@i)));
SET @i = @i + 1;
      END;
END;
GO

The data looks like this now:

--
--SELECT to see how the data looks
--
SELECT * FROM [dbo].[MergeDemo]
GO
ID Description
0 Value for ID is 0
1 Value for ID is 1
2 Value for ID is 2
3 Value for ID is 3
4 Value for ID is 4
5 Value for ID is 5
6 Value for ID is 6
7 Value for ID is 7
8 Value for ID is 8
9 Value for ID is 9
10 Value for ID is 10

Insert/Update (without MERGE)

If a procedure were to programmatically insert a new rows given a new ID and Description, it would look somewhat like this:

--Check if key exists and insert a new row
BEGIN
      DECLARE @Id INT = 15;
      DECLARE @Description nvarchar(50) = 'Value for ID is 15';

      --Check to see if a row exists for the key
      IF EXISTS (SELECT 1
                        FROM [dbo].[MergeDemo]
                        WHERE ID = @Id)

            --Update if key exists
            UPDATE [dbo].[MergeDemo]
            SET Description = @Description
            WHERE ID = @Id;         

      ELSE  

            --Insert if key does not exist
            INSERT INTO [dbo].[MergeDemo](ID, Description)
                  VALUES (@Id, @Description);

END;

This code is doing nothing fancy. All it is doing is the same thing we do a lot of times:

IF (Key Exists)
   UPDATE row for key
ELSE
   INSERT new row with key

Insert/Update (with MERGE)

Let us see how our upsert statement (MERGE statement really) does the same thing which is much simpler

--Check if key exists and insert a new row
BEGIN
      DECLARE @Id INT = 15;
      DECLARE @Description nvarchar(50) = 'Value for ID is 15';
      DECLARE @Changes TABLE(Change VARCHAR(20));

      MERGE INTO [dbo].[MergeDemo] AS demo
      USING (SELECT @Id AS Id) AS newval(Id)
            ON demo.Id = newval.Id
      WHEN MATCHED THEN
            UPDATE SET Description = @Description
      WHEN NOT MATCHED THEN
            INSERT (ID, Description) VALUES (@Id, @Description)
      OUTPUT INSERTED.ID, $action;
END;

When I run it the first time, I get this output

ID $action
15 INSERT

When I run it a second time and so forth, I get

ID $action
15 UPDATE

Notice how simple the syntax was? Let us quickly see the different pieces

MERGE INTO [dbo].[MergeDemo] AS demo The clause that defines the table to INSERT/UDPATE/DELETE
USING (SELECT @Id AS Id) AS newval(Id) This is providing the values or rows that we want to work with. This could take several forms
ON demo.Id = newval.Id This is the columns on which we match to make the decision of MATCHED or NOT MATCHED. Typically, this is the join on all PK columns of table to change
WHEN MATCHED THEN What to do if the join succeeded for the current row
WHEN NOT MATCHED THEN What to do if the join failed for the current row
OUTPUT Optional but this is a very powerful clause in and of itself with tons of available options. In this case we just SELECT the output for display. We could just as easily insert into a another memory table or temp table.

OUPUT clause of MERGE:

Again, this is the most information option within the MERGE statement especially as you learn the various nuances of when it updates/deletes/inserts! I cannot reiterate its importance and the examples I have here use that liberally to show you the effect of running the MERGE.

As noted above:

OUTPUT is optional but this is a very powerful clause in and of itself with tons of available options. In this case we just SELECT the output for display. We could just as easily insert into a another memory table or temp table.

INSERT/UPDATE additional overlapping data:

Now let us say, we want to INSERT/UPDATE the output of this SQL back into the table MergeDemo

SELECT
      ID + 5 AS ID,
      'Value for ID is ' + LTRIM(STR(ID + 5)) AS Description
FROM [dbo].[MergeDemo]
ID Description
5 Value for ID is 5
6 Value for ID is 6
7 Value for ID is 7
8 Value for ID is 8
9 Value for ID is 9
10 Value for ID is 10
11 Value for ID is 11
12 Value for ID is 12
13 Value for ID is 13
14 Value for ID is 14
15 Value for ID is 15
20 Value for ID is 20

Some ID’s already exist and some dont

The values 5 through 10 and the ID 15 already exist. Only the ID’s 11 through 14 and the ID 20 are new. Based on the keys, this is what we want:

ID Description Desired Outcome
5 Value for ID is 5 UPDATE
6 Value for ID is 6 UPDATE
7 Value for ID is 7 UPDATE
8 Value for ID is 8 UPDATE
9 Value for ID is 9 UPDATE
10 Value for ID is 10 UPDATE
11 Value for ID is 11 INSERT
12 Value for ID is 12 INSERT
13 Value for ID is 13 INSERT
14 Value for ID is 14 INSERT
15 Value for ID is 15 UPDATE
20 Value for ID is 20 INSERT

Let us MERGE!!

MERGE INTO [dbo].[MergeDemo] AS demo
      USING (
--This is the SELECT we ran above
                  SELECT
                        ID + 5 AS ID,
                        'Value for ID is ' + LTRIM(STR(ID + 5)) AS Description
                  FROM [dbo].[MergeDemo]
            ) AS newval(Id, Description)
            ON demo.Id = newval.Id
      WHEN MATCHED THEN
            UPDATE SET Description = newval.Description
      WHEN NOT MATCHED THEN
            INSERT (ID, Description) VALUES (newval.Id, newval.Description)
      OUTPUT INSERTED.ID, $action;

It INSERTed or UPDATEd appropriately

…and, as we expected, this is the output of the above command

ID $action
5 UPDATE
6 UPDATE
7 UPDATE
8 UPDATE
9 UPDATE
10 UPDATE
11 INSERT
12 INSERT
13 INSERT
14 INSERT
15 UPDATE
20 INSERT

How is DELETE done with MERGE?

I know that some of you are curious how DELETE is done. Let us say we wanted to DELETE based when MATCHED and INSERT otherwise. Although not very sensible, I used the same MERGE statement as above to illustrate by replacing INSERT with DELETE.

The SELECT portion would now return the following

ID Description
5 Value for ID is 5
6 Value for ID is 6
7 Value for ID is 7
8 Value for ID is 8
9 Value for ID is 9
10 Value for ID is 10
11 Value for ID is 11
12 Value for ID is 12
13 Value for ID is 13
14 Value for ID is 14
15 Value for ID is 15
16 Value for ID is 16
17 Value for ID is 17
18 Value for ID is 18
19 Value for ID is 19
20 Value for ID is 20
25 Value for ID is 25

MERGE with DELETE

MERGE INTO [dbo].[MergeDemo] AS demo
      USING (
                  --This is the SELECT we ran above
                  SELECT
                        ID + 5 AS ID,
                        'Value for ID is ' + LTRIM(STR(ID + 5)) AS Description
                  FROM [dbo].[MergeDemo]
                  ) AS newval(Id, Description)
            ON demo.Id = newval.Id
      WHEN MATCHED THEN
            DELETE
      WHEN NOT MATCHED THEN
            INSERT (ID, Description) VALUES (newval.Id, newval.Description)
      OUTPUT DELETED.ID  DeletedID, INSERTED.ID InsertedOrUpdatedId, $action;

and the output of the above is

DeletedID InsertedOrUpdatedId $action
5 NULL DELETE
6 NULL DELETE
7 NULL DELETE
8 NULL DELETE
9 NULL DELETE
10 NULL DELETE
11 NULL DELETE
12 NULL DELETE
13 NULL DELETE
14 NULL DELETE
15 NULL DELETE
NULL 16 INSERT
NULL 17 INSERT
NULL 18 INSERT
NULL 19 INSERT
20 NULL DELETE
NULL 25 INSERT

SQL Server’s built in INSERTED and DELETED output variables

That also illustrated the nice use of the built in output variables INSERTED and DELETED which give you access to values of the affected columns for either the INSERT or UPDATE operation.

This is now the final table content:

SELECT *
FROM [dbo].[MergeDemo]
GO
ID Description
0 Value for ID is 0
1 Value for ID is 1
2 Value for ID is 2
3 Value for ID is 3
4 Value for ID is 4
16 Value for ID is 16
17 Value for ID is 17
18 Value for ID is 18
19 Value for ID is 19
25 Value for ID is 25

More to MERGE than what meets the eye

Try out the examples above in your SSMS. All you have to do is COPY -> PASTE -> OBSERVE and tinker with the various options to see the effects.

There are a lot of additional clauses you can use with the MERGE statement and they are slightly different between SQL Server and Oracle. Please explore them when you get a chance to exploit the true power.

References:

http://msdn.microsoft.com/en-us/library/bb510625.aspx (MERGE statement)

http://msdn.microsoft.com/en-us/library/ms177564.aspx (OUTPUT INTO clause)

Advertisements

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