InfoTácticas Soluciones

miércoles, 25 de noviembre de 2020

MERGE en SQL Server para Insert, Delete y Update con dos tablas - Práctico

MERGE permite sincronizar dos tablas a través de Insert, Update y Delete en un solo query. Válido para SQL SERVER 2008 o superior.
En un caso normal se tendría que hacer los 3 querys por separado, lo que significaría 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, es muy útil cuando se realizan migraciones de grandes volumenes de datos.
La sintaxis de MERGE es:

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> ];

Analizando la instrucción, podemos desglosar su funcionamiento:

Utiliza la tabla_destino como destino (MERGE INTO)
Define la tabla de la cual provienen los datos, aunque también se puede utilizar un CTE o tabla derivada entre algunas otras opciones. Lo más común es utilizar una tabla.
Utiliza la tabla_origen como origen (USING)
Une ambas tablas por campos: condicion_compara_llaves (ON)
WHEN matched (cuando exista el registro en el destino), realiza acciones como: lo actualiza
NOTA: INSERT no está permitida; es posible utilizar 2 cláusulas WHEN MATCHED, una para utilizar la acción UPDATE y otra para la acción DELETE, la única condicionante es que deben tener filtros si se utilizan ambas
WHEN not matched by target (cuando no exista el registro en el destino), realiza acciones como: lo agrega o inserta
La única operación permitida es un INSERT.
WHEN not matched by source (cuando exista el registro en el destino y no exista en el origen), realiza acciones como: eliminar registros del origen
Cuando la fila existe en la tabla destino pero no en la fuente, no se puede aplicar una operación INSERT pero si UPDATE y DELETE, también se puede declarar dos cláusulas de este tipo al igual que la claúsula WHEN MATCHED, con la misma condicionante que deben tener filtros.

Entonces, no solo evalúa las diferencias, sin que nos permite realizar una acción por cada clase de diferencia que se encuentre.
NO es obligatorio utilizar todas las opciones:si por ejemplo, no queremos actualizar los registros existentes en ambas tablas, no utilizamos la sección WHEN matched.

utilizaremos MERGE para comparar las dos tablas y hacer las acciones correspondientes.

--Sincronizar la tabla TARGET con
--los datos actuales de la tabla SOURCE
MERGE Alumno AS TARGET
USING AlumnoActual 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.Promedio <> SOURCE.Promedio THEN 
   UPDATE SET TARGET.Nombre = SOURCE.Nombre, 
              TARGET.Promedio = SOURCE.Promedio
--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, Promedio) 
   VALUES (SOURCE.Codigo, SOURCE.Nombre, SOURCE.Promedio)
--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.Promedio AS TargetPuntos, 
INSERTED.Codigo AS SourceCodigo, 
INSERTED.Nombre AS SourceNombre, 
INSERTED.Promedio AS SourcePuntos; 
SELECT @@ROWCOUNT;
GO
 
SELECT * FROM Alumno
SELECT * FROM AlumnoActual

una sección OUTPUT, esta es totalmente opcional y ahí podemos ver que acciones se tomó por cada línea afectada.
El resultado de OUTPUT es el siguiente: