SQL Server 2008: Merge Statement

SQL Server 2008 introduced the Merge statement which is commonly also called as Upsert (Update / Insert). It can be used as Insert if the row does not exists and update, if it exists. It can be used as ETL process while fetching data. This feature is useful for developers. It is not required to give any particular join (inner, left outer etc).

Let’s see some advantages for using Merge statement.

With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.

The syntax of Merge statement is as follows :
MERGE source
USING
(SELECT * FROM TARGET)
ON criteria
WHEN MATCHED
THEN UPDATE/DELETE
WHEN TARGET NOT MATCHED
THEN INSERT
WHEN SOURCE NOT MATCHED
THEN UPDATE/DELETE

Let’s see some actual code. It is created using Feb CTP of SQL Server 2008 (CTP 6).

CREATE TABLE source
(ID int NOT NULL,
FullName nvarchar(35))

CREATE TABLE target
(ID int NOT NULL,
FullName nvarchar(35))

INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')

INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(2,'Aishwarya Bachchan')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')

With following statement only the update is done if there is match :

MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName;

SELECT * FROM source
SELECT * FROM [target]

The following will be the actual values in both the tables




Now let’s try insert as well as update with Marge statement. Let’s delete previous records from the tables and continue with fresh data :

DELETE source
DELETE [target]

INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')

INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')

SELECT * FROM source
SELECT * FROM [target]

The select query result will look as follows :




After using Merge with Insert and Delete

MERGE Source src
using (SELECT * FROM [target]) as t
ON src.Id = T.Id
WHEN MATCHED
THEN UPDATE SET src.FullName=t.FullName
WHEN TARGET NOT MATCHED
THEN INSERT VALUES (t.ID, t.FullName);

SELECT * FROM source
SELECT * FROM [target]

The result of the Merge statement will give following output :




Now let’s try all three conditions and see the result. Let’s start fresh with the records again :

DELETE source
DELETE [target]

INSERT INTO Source Values(1,'Madhuri Dixit')
INSERT INTO Source VALUES(2,'Aishwarya Roy')
INSERT INTO Source VALUES(3,'Bipasha Baasu')

INSERT INTO [target] VALUES(1,'Madhuri Nene')
INSERT INTO [target] VALUES(4,'Kareena Kapoor')
INSERT INTO [target] VALUES(5,'Priyanka Chopra')

SELECT * FROM source
SELECT * FROM [target]

The result will be as follows :





Let’s use Merge and see the result





We no longer see record for ID =3 as it gets deleted with this merge statement.

Summary
Do not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.


Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.