miércoles, octubre 16, 2019

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.

En este ejemplo vamos 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 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.

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

La sintaxis de MERGE sería:

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.

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

 

merge201

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.

merge2-03-output

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

merge2-02

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.




Roy Rojashttp://www.dotnetcr.com
Con más de 20 años de experiencia en programación, experto en lenguajes .NET, VB, C#, ASP.NET, Xamarin, XCode, DBA en SQL Server. Creador de dotnetcr.com, sitio web para programadores en español. royrojas.com | dotnetcr.com | GitHub

Redes Sociales

2,793FansMe gusta
340SeguidoresSeguir

Popular esta semana

Llamar funciones JavaScript desde el code-behind en ASP.NET

Explicamos como llamar métodos o funciones de JavaScript en nuestras aplicaciones en ASP.NET, eventos de botones o desde el code-behind.
sql server collation

Conflicto con Collation en consultas utilizando UNION ALL en SQL Server

Cómo solucionar el problema del Collation cuando utilizamos UNION o UNION ALL en consultas en SQL SERVER si estas lo tienen distinto.
ERROR PHP 7.2

Error: PHP Warning: count(): Parameter must be an array or an object that implements...

Como solucionar el error "PHP Warning: count() : Parameter must be an array or an object that implements Countable in..."

Últimos artículos

Ejemplos de Documentos Electrónicos 4.3

En este artículo vamos a brindar varios ejemplos de documentos electrónicos para la versión 4.3 de Factura Electrónica de Costa Rica.
Factura Electrónica Costa Rica

Encabezados del XML Factura Electrónica Costa Rica 4.3

Para generar el XML de la factura electrónica de Costa Rica se debe de seguir un formato específico para que estas sean...
C# - PDF417

Crear código PDF417 con iTextSharp y XZing en C# .NET

Para crear códigos PDF417 existen herramientas que podemos agregar a nuestra aplicaciones. En este ejemplo vamos explicar como utilizar iTextSharp y ZXing.

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 imprevisto. Aquí lo solucionamos.