MERGE en SQL Server para Insert, Delete y Update con dos tablas

Ejemplo práctico usando MERGE para sincronizar dos tablas, Insert, Update y Delete en un solo query. Válido para SQL SERVER 2008 o superior.

En un escenario típico de manejo de datos, es probable que tengamos que realizar cualquiera de las tres acciones (Insert, Update o Delete) sobre una tabla con los datos en otra.

Este ejemplo va a tomar como ejemplo un caso de Usuarios y Usuarios Actualizados, y como resultado nos va a dejar las dos tablas sincronizadas, tabla origen y tabla fuente. En un caso normal esto se tendría que hacer los 3 querys por separado, lo que significa varias consultas y un select para verificar si el dato existe, otro para insertar otro para modificar y otro para borrar. Con MERGE, SQL Server nos permite hacer todo esto en una sola consulta, lo que es mucho más eficiente y utiliza muchísimo menos recursos en el servidor, más aun cuando las tablas son muy grandes.

NOTA: esto solo funciona en versiones SQL 2008 o superior.

SQL Server – Error 3414, servicio no inicia - El error 3414 se produce generalmente cuando el motor de la base de datos SQL Server sufre algún evento…

La sintaxis de MERGE

MERGE <table_destino> [AS TARGET]
USING <table_origen> [AS SOURCE]
   ON <condicion_compara_llaves>
[WHEN MATCHED THEN 
    <accion cuando coinciden> ]
[WHEN NOT MATCHED [BY TARGET] THEN 
    <accion cuando no coinciden por destino> ]
[WHEN NOT MATCHED BY SOURCE THEN 
    <accion cuando no coinciden por origen> ];

Si bien están todos los posibles escenarios no es requisito utilizarlas todas, o bien tampoco en el mismo orden o función que le daremos en este ejemplo, usted puede hacer lo que necesite.

La instrucción MERGE sincroniza los datos de un origen establecido en una tabla destino, basado en la condición (tipo “where”) que se indique y si estos datos desde el origen existen o no en el destino. Si los datos coinciden  por el primer filtro llega a la opción “WHEN MATCHED” donde aún ahí se le puede agregar otro filtro “WHEN MATCHED AND TARGET.Nombre <> SOURCE.Nombre” o “WHEN MATCHED AND TARGET.Puntos > 5”. Si los datos están en SOURCE pero no están en TARGET “WHEN NOT MATCHED BY TARGET THEN” o si los datos están en TARGET pero no en SOURCE “WHEN NOT MATCHED BY SOURCE THEN”.

Esto puede resultar muy útil a la hora de sincronizar datos no actualizados, o si queremos tener tablas de almacenamiento o archivo histórico, más aun si estas ya cuentan con muchos registros.

Para poner esto en práctica vamos a tomar un ejemplo de usuarios con un respectivo puntaje. Si estos usuarios han tenido cambios en su nombre o puntaje se actualizan, si el usuario es nuevo se inserta y si el usuario no ha tenido movimientos se borra de la tabla target.

Creando ejemplo

Primero vamos a crear las tablas y datos de prueba.

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

Ejecutar MERGER

Ahora vamos a utilizar MERGE para comparar las dos tablas y hacer las acciones correspondientes.

--Sincronizar la tabla TARGET con
--los datos actuales de la tabla SOURCE
MERGE Usuarios AS TARGET
USING UsuariosActual AS SOURCE 
   ON (TARGET.Codigo = SOURCE.Codigo) 
--Cuandos los registros concuerdan con por la llave
--se actualizan los registros si tienen alguna variación
 WHEN MATCHED AND TARGET.Nombre <> SOURCE.Nombre 
   OR TARGET.Puntos <> SOURCE.Puntos THEN 
   UPDATE SET TARGET.Nombre = SOURCE.Nombre, 
              TARGET.Puntos = SOURCE.Puntos 
--Cuando los registros no concuerdan por la llave
--indica que es un dato nuevo, se inserta el registro
--en la tabla TARGET proveniente de la tabla SOURCE
 WHEN NOT MATCHED BY TARGET THEN 
   INSERT (Codigo, Nombre, Puntos) 
   VALUES (SOURCE.Codigo, SOURCE.Nombre, SOURCE.Puntos)
--Cuando el registro existe en TARGET y no existe en SOURCE
--se borra el registro en TARGET
 WHEN NOT MATCHED BY SOURCE THEN 
   DELETE
 
--Seccion opcional e informativa
--$action indica el tipo de accion
--en OUTPUT retorna cualquiera de las 3 acciones 
--'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

En el query tenemos una sección OUTPUT, esta es totalmente opcional y ahí podemos ver que acciones se tomó por cada línea afectada. Si bien también se podría usar para alguna tabla de bitácora o simplemente no usarla y solamente para cuando se hacen pruebas.

El resultado en output nos muestra línea por línea que acción tomó nuestro query, si actualizó, borró o insertó. Repetimos, esta sección es opcional.

Ahora el resultado de las dos tablas después de usar MERGE

Como podemos ver las dos tablas quedaron sincronizadas, se actualizaron los datos en la tabla TARGET y el usuario que no tenía movimientos de puntaje fue eliminado.

Este es un ejemplo pero los posibles escenarios que podemos tener son muchísimos, y las ventajas que nos da son mucho mayores. Espero que en algún momento esto les sea de utilidad.