Output en Merge SQL Server

Usando Output de Merge

La instrucción Merge realiza instrucciones de inserción de registros, actualización o eliminación de registros en una tabla de destino en la misma base de datos o en otra base de datos según los resultados de combinar los registros con una tabla de origen.

Para más información ver:

Merge en SQL Server
Merge con Select
Merge en Graph Tables
Insertar registros
Actualizar registros
Variables tipo tabla
Triggers
Crear tablas

En este artículo se va explicar el uso de la cláusula Output en Merge para poder visualizar la acción realizada en cada registro tomado en cuenta en la instrucción. Se sugiere utilizar esta opción para poder analizar el resultado de la instrucción Merge en un entorno de desarrollo antes de pasar a producción cualquier proceso.

Usando la base de datos Northwind

use northwind
go

Ejercicio 1

Crearemos dos tablas de Clientes, una con los datos no actualizados y otra con los datos actualizados. Luego se va a ejecutar el Merge mostrando las acciones usando la cláusula Output.

Create table Clientes
(
ClientesCodigo nchar(5),
ClientesRazonSocial nvarchar(100),
ClientesDireccion nvarchar(100),
ClientesMontoCredito Numeric(9,2),
constraint ClientesPK primary key (ClientesCodigo)
)
go

Insertamos Clientes a la tabla

Insert into Clientes Values
(‘00001′,’Fernando Luque’,’Av. San Bartolo 3994′,15000),
(‘00002′,’Raquel Terranova’,’Av. Los Paramos 4456′,5000),
(‘00003′,’Carlos Mendoza’,’Av. Brasil 445′, 3500),
(‘00004′,’Lucas Maldonado’,’Av. La República 643′, 10000),
(‘00005′,’Ingrid Chávez’,’Av. Loreto 994′, 3800),
(‘00006′,’Rossana Moreno’,’Av. La Mar 464′, 12000)
go
Listado de los clientes
select * from clientes
go
El resultado se muestra en la siguiente imagen

Tabla de Clientes llamada ClientesNueva con actualizaciones, clientes nuevos y clientes que no existen en la tabla inicial.

Create table ClientesNueva
(
ClientesNuevaCodigo nchar(5),
ClientesNuevaRazonSocial nvarchar(100),
ClientesNuevaDireccion nvarchar(100),
ClientesNuevaMontoCredito Numeric(9,2),
constraint ClientesNuevaPK primary key (ClientesNuevaCodigo)
)
go

Insertar datos en ClientesNueva, note que existen clientes en la tabla ClientesNueva que no existen en la tabla Clientes, otros que están con los datos cambiados y hay clientes en la tabla ClientesNueva que no existe en la tabla Clientes.

Insert into ClientesNueva Values
(‘00001′,’Fernando Luque’,’Av. San Juan 2123′,18000),
(‘00002′,’Raquel Terranova Llanos’,’Av. Los Paramos 4456′,8000),
(‘00004′,’Lucas Maldonado’,’Av. La República 643′, 10000),
(‘00005′,’Ingrid Chávez’,’Av. Loreto 994′, 25000),
(‘00006′,’Rossana Moreno’,’Av. La Mar 464′, 12000),
(‘00007′,’Cecilia Pérez Casós’,’Av. Los Laureles 963′, 2000),
(‘00008′,’Luis Mendiola Sánchez’,’Av. Policial 1464′, 18000)
go
Listado de ClientesNueva
Select * from ClientesNueva
go
El resultado se muestra en la siguiente imagen

Tabla: ClientesNueva

Note lo siguiente:
Clientes nuevos: clientes con códigos 00007 y 00008
Clientes con datos actualizados: clientes con códigos 00001, 00002 y 00005
Clientes que no figuran en la tabla nueva: cliente con código 00003
La imagen siguiente muestra la comparación de las dos tablas.

Usando el Merge y mostrando las accciones realizadas.
Tabla que se va a sincronizar: Tabla destino Clientes usando como origen ClientesNueva.

Merge
Clientes USING ClientesNueva
ON Clientes.ClientesCodigo = ClientesNueva.ClientesNuevaCodigo
When MATCHED THEN
UPDATE SET Clientes.ClientesRazonSocial = ClientesNueva.ClientesNuevaRazonSocial,
Clientes.ClientesDireccion = ClientesNueva.ClientesNuevaDireccion ,
Clientes.ClientesMontoCredito = ClientesNueva.ClientesNuevaMontoCredito
When NOT MATCHED by Target THEN
INSERT (ClientesCodigo, ClientesRazonSocial,
ClientesDireccion, ClientesMontoCredito)
VALUES (ClientesNueva.ClientesNuevaCodigo, ClientesNueva.ClientesNuevaRazonSocial,
ClientesNueva.ClientesNuevaDireccion, ClientesNueva.ClientesNuevaMontoCredito)
When NOT MATCHED BY Source THEN
DELETE
OUTPUT $action As ‘Acción’,
Inserted.ClientesCodigo As ‘Código’,
Inserted.ClientesRazonSocial As ‘Nombre’,
Inserted.ClientesDireccion As ‘Dirección’,
Inserted.ClientesMontoCredito As ‘Crédito’,
Deleted.ClientesCodigo As ‘Código’,
Deleted.ClientesRazonSocial As ‘Nombre’,
Deleted.ClientesDireccion As ‘Dirección’,
Deleted.ClientesMontoCredito As ‘Crédito’;
SELECT @@ROWCOUNT;

go
El resultado se muestra en la siguiente imagen

Resultado de la instrucción Merge

Note lo siguiente:

Los clientes con códigos 00001, 00002, 00004, 00005 y 00006 se han actualizado, en la primera columna que muestra la acción realizada aparace UPDATE. Los clientes con códigos 00007 y 00008 se han insertado, en la columna acción aparece INSERT. El cliente con código 00003 se ha eliminado, en la columna acción aparece DELETE.

Mostramos las dos tablas.
Select * From Clientes
Select * From ClientesNueva
go
El resultado se muestra en la siguiente imagen
Note que las dos tablas tiene los mismos registros.

Resultado en las tablas Clientes y ClientesNueva después del Merge

Ejercicio 2

Crearemos dos tablas Ubicacion y UbicacionVisita, luego se van a sincronizar usando Merge.

Create table Ubicacion
(
UbicacionCodigo int,
UbicacionDireccion nvarchar(100)
)
go

Create table UbicacionVisita
(
UbicacionCodigo int,
UbicacionDireccion nvarchar(100)
)
go

Insertamos datos
Insert into Ubicacion values
(1,’Trujillo Centro’),
(2,’Lima La Victoria’) ,
(3,’Piura San Cristobal’)
go

Insert into UbicacionVisita values
(1,’Trujillo Victor Larco’) ,
(3,’Cajamarca Baños del Inca’),
(4,’Cusco San Blas’)
go

Usando el Merge y la cláusula Output

MERGE Ubicacion D
USING UbicacionVisita O ON D.UbicacionCodigo=O.UbicacionCodigo
WHEN MATCHED THEN
UPDATE SET UbicacionDireccion=O.UbicacionDireccion
WHEN NOT MATCHED By TARGET THEN
INSERT (UbicacionCodigo,UbicacionDireccion)
VALUES (O.UbicacionCodigo,O.UbicacionDireccion)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT Deleted., $action As ‘Acción’, Inserted. ;
go
El resultado se muestra en la siguiente imagen

El resultado del Output muestra que se ha insertado la ubicación «Cusco San Blas» con el código 4, se ha actualizado el registro con código 1, los datos cambiados y borrados fueron «Trujillo Centro» y se actualizaron por «Trujillo Victor Larco», el registro con código 2 fue eliminado y el registro con código 3 fue actualizado, el dato de «Piura San Cristobal» fue cambiado por «Cajamarca Baños del Inca»

Consideraciones importantes en Merge

  • Para ejecutar la instrucción MERGE debe tener el permiso SELECT en la tabla SOURCE y los permisos INSERT, UPDATE y DELETE en la tabla TARGET.
  • La instrucción MERGE requiere un punto y coma (;) al finalizar la instrucción. De lo contrario, se genera un error cuando una sentencia MERGE no termina con punto y coma.
  • Se puede usar @@ROWCOUNT después de Merge para obtener el número total de filas insertadas, actualizadas y eliminadas.
  • La instrucción MERGE mejora el rendimiento ya que todos los datos se leen y procesan solo una vez, mientras que en versiones anteriores se deben escribir tres declaraciones diferentes para procesar tres actividades diferentes (INSERT, UPDATE o DELETE), en cuyo caso los datos en las tablas de origen y destino se evalúan y procesan varias veces; al menos una vez por cada declaración.
  • Se debe especificar al menos una de las tres cláusulas MATCHED cuando se usa la instrucción MERGE; las cláusulas MATCHED se pueden especificar en cualquier orden.
  • Para cada acción INSERT, UPDATE o DELETE especificada en la instrucción MERGE, SQL Server dispara los Triggers AFTER correspondientes definidos en la tabla de destino, pero no garantiza el orden. Los activadores definidos para la misma acción respetan el orden que especifique.

Ejercicio 3

Merge con Output usando variables tipo tabla.
En este ejercicio se va a crear una tabla de productos, luego se va a usar la instrucción Merge teniendo en cuenta los datos de una variable tipo tabla.

Create table ProductosMerge
(
Codigo int,
Descripcion nvarchar(50),
Stock Numeric(9,2),
Precio Numeric(9,2),
constraint ProductosMergePK primary key (Codigo)
)

Insertamos productos

Insert into ProductosMerge values
( 1 , ‘Teclado’ , 10 , 55 ),
( 2 , ‘Monitor’ , 5 , 220),
( 3 , ‘Mouse’ , 50 , 25 ),
( 4 , ‘Impresora’ , 15, 350 )
go

Listado de los productos
select * from ProductosMerge
go
El resultado se muestra en la siguiente imagen

Se va a declarar una variable tipo tabla con los datos a actualizar.
DECLARE @Productos Table
(
Codigo int,
Descripcion nvarchar(50),
Stock Numeric(9,2),
Precio Numeric(9,2)
)
Insert into @Productos
Select 1 As Codigo, ‘Teclado Gamer’ As Descripcion, 15 As Stock, 70 As Precio
Union
Select 3, ‘Mouse Shadow Optical’ , 55 , 30
Union
SELECT 4, ‘Impresora Epson L355’ , 12 , 450
Union
SELECT 5, ‘Laptop Samsung Core i7’ , 5 , 1200
Union
SELECT 6, ‘Escritorio gerencial 1.40’ , 2 , 650
Merge into ProductosMerge As D
USING @Productos As O
ON D.Codigo = O.Codigo
When Matched and
(D.Descripcion <> O.Descripcion or D.Stock <> O.Stock or D.Precio <> O.Precio)
Then UPDATE
SET D.Descripcion = O.Descripcion, D.Stock = O.Stock, D.Precio = O.Precio
When NOT Matched by Target then
Insert values ( O.Codigo, O.Descripcion, O.Stock, O.Precio)
When NOT Matched BY Source
Then DELETE
OUTPUT $action As ‘Acción’,
inserted.Descripcion As ‘Nueva Descripción’,
inserted.Stock As ‘Nuevo Stock’,
inserted.Precio As ‘Nuevo Precio’,
deleted.Descripcion As ‘Descripción anterior’,
deleted.Stock As ‘Stock anterior’,
deleted.Precio As ‘Precio anterior’;
go

La salida del OutPut se muestra en la siguiente imagen

Listado de la tabla ProductosMerge
SELECT * FROM ProductosMerge
go
El resultado se muestra en la siguiente imagen