MERGE in SQL Server for Insert, Delete and Update with two tables

Practical example using MERGE to synchronize two tables, Insert, Update and Delete in a single query. Valid for SQL SERVER 2008 or higher.

In a typical data management scenario, we are likely to have to perform any of the three actions (Insert, Update or Delete) on a table with data in another.

This example is going to take as an example a case of Users and Updated Users, and as a result it is going to leave us with the two tables synchronized, source table and target table. In a normal case this would have to be done with 3 separate queries, which means several queries and a select to verify if the data exists, another one to insert another one to modify and another one to delete. With MERGE, SQL Server allows us to do all this in a single query, which is much more efficient and uses much less resources on the server, even more so when the tables are very large.

NOTE: this only works in SQL 2008 or higher versions.

The MERGE syntax

MERGE <table_target> [AS TARGET]
USING <table_source> [AS SOURCE]
   ON <merge_search_condition>
[WHEN MATCHED THEN 
    <action when match> ]
[WHEN NOT MATCHED [BY TARGET] THEN 
    <action when they do not match by target> ]
[WHEN NOT MATCHED BY SOURCE THEN 
    <action when they do not match by source> ];

While all possible scenarios are there, it is not required to use them all, or even in the same order or function that we will give them in this example, you can do what you need.

The MERGE statement synchronizes data from an established source into a destination table, based on the condition (type «where») indicated and whether or not this data from the source exists in the destination. If the data matches by the first filter it reaches the «WHEN MATCHED» option where another filter can still be added «WHEN MATCHED AND TARGET.Name <> SOURCE.Name» or «WHEN MATCHED AND TARGET.Points > 5». If the data is in SOURCE but not in TARGET «WHEN NOT MATCHED BY TARGET THEN» or if the data is in TARGET but not in SOURCE «WHEN NOT MATCHED BY SOURCE THEN».

This can be very useful when synchronizing non-updated data, or if we want to have storage tables or historical files, even more so if these already have many records.

To put this into practice we are going to take an example of users with a respective score. If these users have had changes in their name or score they are updated, if the user is new they are inserted and if the user has had no movements they are deleted from the target table.

Creating example

First we are going to create the test tables and data.

CREATE TABLE Usuarios
(
Codigo INT PRIMARY KEY,
Nombre VARCHAR(100),
Puntos INT
) 
GO
INSERT INTO Usuarios VALUES
(1,'Juan Perez',10),
(2,'Marco Salgado',5),
(3,'Carlos Soto',9),
(4,'Alberto Ruiz',12),
(5,'Alejandro Castro',5)
GO
CREATE TABLE UsuariosActual
(
Codigo INT PRIMARY KEY,
Nombre VARCHAR(100),
Puntos INT
) 
GO
INSERT INTO UsuariosActual VALUES
(1,'Juan Perez',12),
(2,'Marco Salgado',11),
(4,'Alberto Ruiz Castro',4),
(5,'Alejandro Castro',5),
(6,'Pablo Ramos',8)
 
SELECT * FROM Usuarios
SELECT * FROM UsuariosActual

How to use MERGE?

Now we are going to use MERGE to compare the two tables and perform the corresponding actions.

--Synchronize TARGET table with
--the current data from the SOURCE table
MERGE Usuarios AS TARGET
USING UsuariosActual AS SOURCE 
   ON (TARGET.Codigo = SOURCE.Codigo) 
--When records match by key
--records are updated if they have any variation
 WHEN MATCHED AND TARGET.Nombre <> SOURCE.Nombre 
   OR TARGET.Puntos <> SOURCE.Puntos THEN 
   UPDATE SET TARGET.Nombre = SOURCE.Nombre, 
              TARGET.Puntos = SOURCE.Puntos 
--When the registers do not agree by the key
--indicates that it is a new data, the record is inserted
--in the TARGET table from the SOURCE table
 WHEN NOT MATCHED BY TARGET THEN 
   INSERT (Codigo, Nombre, Puntos) 
   VALUES (SOURCE.Codigo, SOURCE.Nombre, SOURCE.Puntos)
--When the record exists in TARGET and does not exist in SOURCE
--the record in TARGET is deleted
 WHEN NOT MATCHED BY SOURCE THEN 
   DELETE
 
--Additional and informative section
--$action indicates the type of action
--in OUTPUT return any of the 3 actions
--'INSERT', 'UPDATE', or 'DELETE', 
OUTPUT $action, 
DELETED.Codigo AS TargetCodigo, 
DELETED.Nombre AS TargetNombre, 
DELETED.Puntos AS TargetPuntos, 
INSERTED.Codigo AS SourceCodigo, 
INSERTED.Nombre AS SourceNombre, 
INSERTED.Puntos AS SourcePuntos; 
SELECT @@ROWCOUNT;
GO
 
SELECT * FROM Usuarios
SELECT * FROM UsuariosActual

In the query we have an OUTPUT section, this is totally optional and there we can see what actions were taken for each affected line. Although it could also be used for some log table or simply not used at all and only for testing purposes.

The output result shows us line by line what action our query took, whether it updated, deleted or inserted. We repeat, this section is optional.

Now the result of the two tables after using MERGE

As we can see both tables were synchronized, data was updated in TARGET table and user who had no score movements was deleted.

This is an example but there are many possible scenarios that we can have, and the advantages it gives us are much greater. I hope that at some point this will be useful for you.