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)
SQL Jana 