Insertar, actualizar y eliminar datos mediante MERGE
En SQL Server 2008, se pueden realizar operaciones de inserción, actualización o eliminación en una sola instrucción utilizando la instrucción MERGE. La instrucción MERGE le permite combinar un origen de datos con una tabla o vista de destino y, a continuación, realizar varias acciones con el destino según los resultados de esa combinación. Por ejemplo, puede utilizar la instrucción MERGE para realizar las operaciones siguientes:
Condicionalmente insertar o actualizar filas en una tabla de destino.
Si la fila existe en la tabla de destino, actualizar una o varias columnas; de lo contrario, insertar los datos en una fila nueva.
Sincronizar dos tablas.
Insertar, actualizar o eliminar filas en una tabla de destino según las diferencias con los datos de origen.
La sintaxis de MERGE está compuesta de cinco cláusulas principales:
La cláusula MERGE especifica la tabla o vista que es el destino de las operaciones de inserción, actualización o eliminación.
La cláusula USING especifica el origen de datos que va a combinarse con el destino.
La cláusula ON especifica las condiciones de combinación que determinan las coincidencias entre el destino y el origen.
Las cláusulas WHEN (WHEN MATCHED, WHEN NOT MATCHED BY TARGET y WHEN NOT MATCHED BY SOURCE) especifican las acciones que se van a llevar a cabo según los resultados de la cláusula ON y cualquier criterio de búsqueda adicional especificado en las cláusulas WHEN.
La cláusula OUTPUT devuelve una fila por cada fila del destino que se inserta, actualiza o elimina.
Para obtener detalles completos de la sintaxis y las reglas, vea MERGE (Transact-SQL).
Especificar las condiciones de búsqueda de origen y destino
Es importante comprender cómo se combinan los datos de destino y origen en una secuencia de entrada única y cómo se pueden usar criterios de búsqueda adicionales para filtrar correctamente las filas innecesarias. De lo contrario, podría especificar un criterio de búsqueda adicional de manera que genere los resultados incorrectos.
Las filas en el origen coinciden con las filas en el destino según en el predicado de combinación especificado en la cláusula ON. El resultado es una secuencia de entrada combinada. Se realiza una operación de inserción, actualización o de eliminación por cada fila de entrada. Dependiendo de las cláusulas WHEN especificadas en la instrucción, la fila de entrada podría ser una de las siguientes:
Un par coincidente compuesto de una fila del destino y una del origen. Éste es el resultado de la cláusula WHEN MATCHED.
Una fila del origen que no tiene ninguna fila correspondiente en el destino. Éste es el resultado de la cláusula WHEN NOT MATCHED BY TARGET.
Una fila del destino que no tiene ninguna fila correspondiente en el origen. Éste es el resultado de la cláusula WHEN NOT MATCHED BY SOURCE.
La combinación de las cláusulas WHEN especificadas en la instrucción MERGE determina el tipo de combinación que implementa el procesador de consultas y que afecta a la secuencia de entrada resultante. Como ejemplo, tenga en cuenta las siguientes tablas de origen y de destino de ejemplo, así como los datos.
USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
La tabla siguiente enumera los posibles tipos de combinación e indica cuando el optimizador de consultas implementa cada tipo. La tabla también muestra la secuencia de entrada resultante para las tablas de origen y de destino de ejemplo cuando el criterio de búsqueda para que coincidan los datos de origen y de destino es Source.EmployeeID = Target.EmployeeID.
Tipo de combinación |
Implementación |
Resultados de secuencia de entrada de ejemplo |
|---|---|---|
INNER JOIN |
La cláusula WHEN MATCHED es la única cláusula WHEN especificada. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- ------- NULL NULL NULL NULL |
LEFT OUTER JOIN |
Se especifica la cláusula WHEN NOT MATCHED BY TARGET pero no se especifica la cláusula WHEN NOT MATCHED BY SOURCE. La cláusula WHEN MATCHED se podría o no especificar. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL |
RIGHT OUTER JOIN |
Se especifican las cláusulas WHEN MATCHED y WHEN NOT MATCHED BY SOURCE pero no es especifica la cláusula WHEN NOT MATCHED BY TARGET. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------NULL NULL 103 Bob NULL NULL 104 Steve |
FULL OUTER JOIN |
Se especifican las cláusulas WHEN NOT MATCHED BY TARGET y WHEN NOT MATCHED BY SOURCE. La cláusula WHEN MATCHED se podría o no especificar. |
SrcEmpID SrcName TrgEmpID TrgName -------- ------- -------- -------100 Mary NULL NULL 101 Sara NULL NULL 102 Stefano NULL NULL NULL NULL 103 Bob NULL NULL 104 Steve |
ANTI SEMI JOIN |
La cláusula WHEN NOT MATCHED BY SOURCE es la única cláusula WHEN especificada. |
TrgEmpID TrgName -------- ------- 100 Mary 101 Sara 102 Stefano |
Los resultados de la secuencia de entrada de ejemplo muestran que los resultados de la secuencia de entrada dependen de la combinación de las cláusulas WHEN. Ahora suponga que desea realizar las acciones siguientes en la tabla de destino según esa secuencia de entrada:
Insertar filas de la tabla de origen si el identificador del empleado no existe en la tabla de destino y el nombre del empleado de origen empieza con 'S'.
Eliminar filas en la tabla de destino si el nombre del empleado de destino empieza con 'S' y el identificador del empleado no existe en la tabla de origen.
Para realizar estas acciones, son necesarias las siguientes cláusulas WHEN:
WHEN NOT MATCHED BY TARGET THEN INSERT
WHEN NOT MATCHED BY SOURCE THEN DELETE
Tal y como se describe en la tabla anterior, cuando se especifican ambas cláusulas WHEN NOT MATCHED, la secuencia de entrada resultante es una combinación externa completa de las tablas de destino y de origen. Ahora que se conocen los resultados de la secuencia de entrada, debe tener en cuenta cómo se aplicarán las acciones de inserción, actualización y eliminación en la secuencia de entrada.
Como se ha indicado anteriormente, las cláusulas WHEN especifican las acciones que se deben realizar según los resultados de la cláusula ON y cualquier criterio de búsqueda adicional especificados en las cláusulas WHEN. En muchos casos, las condiciones de búsqueda especificadas en la cláusula ON generan la secuencia de entrada necesaria. Sin embargo, en el ejemplo, las acciones de inserción y eliminación exigen un filtrado adicional para restringir las filas afectadas a aquéllas con un nombre de empleado que empieza con 'S'. En el ejemplo siguiente, las condiciones de filtrado se aplican a WHEN NOT MATCHED BY TARGET y WHEN NOT MATCHED BY SOURCE. La salida de la instrucción muestra que las filas esperadas de la secuencia de entrada se corrigen, insertan o eliminan.
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO
A continuación se muestran los resultados de la cláusula OUTPUT.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 104 Steve NULL NULL
(3 filas afectadas)
Cuando se reduce el número de filas en la secuencia de entrada al principio del proceso, especificando la condición de búsqueda adicional a la cláusula ON (por ejemplo, especificando ON Source.EmployeeID = Target.EmployeeID AND EmployeeName LIKE 'S%'), podría parecer que mejora el rendimiento de las consultas. Sin embargo, esto puede producir resultados inesperados e incorrectos. Dado que las condiciones de búsqueda adicionales especificadas en la cláusula ON no se usan para que coincidan los datos de destino y de origen, pueden aplicarse mal.
El ejemplo siguiente muestra cómo se pueden producir resultados incorrectos. La condición de búsqueda para que coincidan las tablas de destino y de origen, y la condición de búsqueda adicional para filtrar las filas, se especifican en la cláusula ON. Puesto que no se necesita la condición de búsqueda adicional para determinar la coincidencia de origen y de destino, se aplican las acciones de inserción y eliminación en todas las filas de entrada. De hecho, se omite la condición de filtrado EmployeeName LIKE 'S%'. Cuando se ejecuta la instrucción, la salida de las tablas inserted y deleted muestra que se han modificado dos filas de manera incorrecta: Mary incorrectamente se elimina de la tabla de destino y Bob incorrectamente se inserta.
-- MERGE statement with join conditions that produce unexpected results.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID AND T.EmployeeName LIKE 'S%'
AND S.EmployeeName LIKE 'S%' )
WHEN NOT MATCHED BY TARGET
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
ROLLBACK TRAN;
GO
A continuación se muestran los resultados de la cláusula OUTPUT.
$action EmployeeID EmployeeName EmployeeID EmployeeName
--------- ----------- ------------ ----------- ------------
DELETE NULL NULL 100 Mary
DELETE NULL NULL 101 Sara
DELETE NULL NULL 102 Stefano
INSERT 103 Bob NULL NULL
INSERT 104 Steve NULL NULL
(5 filas afectadas)
Instrucciones de condiciones de búsqueda
Las condiciones de búsqueda que se usan para que coincidan las filas de origen y de destino, así como las condiciones de búsqueda adicionales que se usan para filtrar las filas de origen o de destino, se deben especificar correctamente para asegurarse de que se obtienen resultados correctos. Se recomienda seguir estas instrucciones:
Especifique únicamente las condiciones de búsqueda en la cláusula ON <merge_search_condition> que determinan los criterios para que coincidan los datos en las tablas de origen y de destino. Es decir, especifique solo las columnas de la tabla de destino que se comparan con las correspondientes columnas de la tabla de origen.
No incluya comparaciones a otros valores como una constante.
Para filtrar las filas de las tablas de origen o de destino, use uno de los métodos siguientes:
Especifique la condición de búsqueda para el filtrado de filas en cláusula WHEN adecuada. Por ejemplo, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Defina una vista en el origen o destino que devuelva las filas filtradas y haga referencia a la vista como la tabla de origen o de destino. Si se define la vista en la tabla de destino, cualquier acción con ella debe satisfacer las condiciones para actualizar las vistas. Para obtener más información acerca de cómo actualizar datos mediante una vista, vea Modificar datos mediante una vista.
Use la cláusula WITH <expresión de tabla común> para filtrar filas de las tablas de origen o de destino. Este método es similar a especificar el criterio de búsqueda adicional en la cláusula ON y puede generar resultados incorrectos. Se recomienda evitar el uso de este método o prueba de manera exhaustiva antes de implementarlo.
Ejemplos
A. Usar una instrucción MERGE simple para realizar operaciones INSERT y UPDATE
Suponga que tiene una tabla FactBuyingHabits en una base de datos de almacenamiento de datos que realiza el seguimiento de la fecha más reciente en que cada cliente compró un determinado producto. Una segunda tabla, Purchases, que se hospeda en una base de datos OLTP, registra las compras realizadas durante una semana determinada. Desea agregar cada semana las filas de los productos que determinados clientes no han comprado nunca de la tabla Purchases a la tabla FactBuyingHabits. En el caso de las filas de clientes que compran productos que ya han comprado antes, simplemente desea actualizar la fecha de compra en la tabla FactBuyingHabits. Estas operaciones de inserción y actualización se pueden realizar en una sola instrucción utilizando MERGE.
El ejemplo siguiente crea en primer lugar las tablas Purchases y FactBuyingHabits y a continuación carga en ellas algunos datos de muestra. El rendimiento de las instrucciones MERGE mejora cuando se crean índices UNIQUE en la clave de combinación, de modo que los índices clúster se crean creando una restricción KEY PRIMARY en la columna ProductID en ambas tablas.
En este ejemplo, Purchases contiene las compras durante la semana del 21 de agosto de 2006. FactBuyingHabits contiene las compras correspondientes a la semana anterior; por lo general esta tabla se rellenaría con las filas de fechas muy anteriores.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Purchases', N'U') IS NOT NULL
DROP TABLE dbo.Purchases;
GO
CREATE TABLE dbo.Purchases (
ProductID int, CustomerID int, PurchaseDate datetime,
CONSTRAINT PK_PurchProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.Purchases VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO
IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO
CREATE TABLE dbo.FactBuyingHabits (
ProductID int, CustomerID int, LastPurchaseDate datetime,
CONSTRAINT PK_FactProdID PRIMARY KEY(ProductID,CustomerID));
GO
INSERT INTO dbo.FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO
Las tablas se rellenan ahora con los datos siguientes:
dbo.Purchases
ProductID CustomerID PurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-20 00:00:00.000
707 15160 2006-08-25 00:00:00.000
708 18529 2006-08-21 00:00:00.000
711 11794 2006-08-20 00:00:00.000
711 19585 2006-08-22 00:00:00.000
712 14680 2006-08-26 00:00:00.000
712 21524 2006-08-26 00:00:00.000
712 19072 2006-08-20 00:00:00.000
870 15160 2006-08-23 00:00:00.000
870 11927 2006-08-24 00:00:00.000
870 18749 2006-08-25 00:00:00.000
dbo.FactBuyingHabits
ProductID CustomerID LastPurchaseDate
----------- ----------- -----------------------
707 11794 2006-08-14 00:00:00.000
707 18178 2006-08-18 00:00:00.000
864 14114 2006-08-18 00:00:00.000
866 13350 2006-08-18 00:00:00.000
866 20201 2006-08-15 00:00:00.000
867 20201 2006-08-14 00:00:00.000
869 19893 2006-08-15 00:00:00.000
870 17151 2006-08-18 00:00:00.000
870 15160 2006-08-17 00:00:00.000
871 21717 2006-08-17 00:00:00.000
871 21163 2006-08-15 00:00:00.000
871 13350 2006-08-15 00:00:00.000
873 23381 2006-08-15 00:00:00.000
Observe que hay dos filas Producto-Cliente en común las dos tablas: el cliente 11794 compró el producto 707 durante la semana actual y en la semana anterior, y lo mismo puede decirse del cliente 15160 respecto al producto 870. Para esas filas, actualizamos FactBuyingHabits con la fecha registrada para esas compras en Purchases utilizando la cláusula WHEN MATCHED THEN. Insertamos todas las demás filas en FactBuyingHabits utilizando la cláusula WHEN NOT MATCHED THEN.
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;
B. Realizar las operaciones UPDATE y DELETE
El siguiente ejemplo usa MERGE para actualizar a diario la tabla ProductInventory de la base de datos de muestra AdventureWorks, en función de los pedidos procesados en la tabla SalesOrderDetail. Utilizando la siguiente instrucción MERGE, la columna Quantity de la tabla ProductInventory se actualiza restando el número de pedidos realizados cada día para cada producto. Si el número de pedidos para un producto hace que el inventario del producto llegue a 0 o menos, la fila correspondiente a ese producto se elimina de la tabla ProductInventory. Observe que la tabla de origen se agrega en la columna ProductID. Si no se hiciera, más de un ProductID de la tabla de origen podría coincidir con la tabla de destino y hacer que la instrucción MERGE devuelva un error.
USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
@OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
THEN DELETE
WHEN MATCHED
THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
Deleted.Quantity, Deleted.ModifiedDate;
GO
EXECUTE Production.usp_UpdateInventory '20030501'
C. Realizar las operaciones INSERT, UPDATE y DELETE
El ejemplo siguiente utiliza MERGE para insertar, actualizar o eliminar las filas de una tabla de destino según las diferencias con los datos de origen. Considere una pequeña compañía con cinco departamentos, cada uno con un gerente de departamento. La compañía decide reorganizar sus departamentos. Para implementar los resultados de la reorganización en la tabla de destino dbo.Departments, la instrucción MERGE debe implementar los cambios siguientes:
Algunos de los departamentos existentes no cambiarán.
Algunos departamentos existentes tendrán nuevos gerentes.
Se crearán algunos departamentos nuevos.
Algunos departamentos dejarán de existir tras la reorganización.
El código siguiente crea la tabla de destino dbo.Departments y la rellena con los gerentes.
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');
Los cambios de la organización que se van a realizar en los departamentos están almacenados en la tabla de origen dbo.Departments_delta. El código siguiente crea y rellena esta tabla:
USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL
DROP TABLE dbo.Departments_delta;
GO
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
Manager nvarchar(50));
GO
INSERT INTO dbo.Departments_delta VALUES
(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO
Finalmente, para reflejar la reorganización de la compañía en la tabla de destino, el código siguiente utiliza la instrucción MERGE para comparar la tabla de origen, dbo.Departments_delta, con la tabla de destino dbo.Departments. La condición de búsqueda para esta comparación se define en la cláusula ON de la instrucción. Según los resultados de la comparación, se toman las medidas siguientes.
Los departamentos que existan en ambas tablas se actualizarán en la tabla de destino con los nuevos nombres, los nuevos gerentes o ambos en la tabla Departments. Si no hay ningún cambio, no se realizará ninguna actualización. Esto se logra en la cláusula WHEN MATCHED THEN.
Cualquier departamento de Departments_delta que no exista en Departments se inserta en Departments. Esto se lleva a cabo en la cláusula WHEN NOT MATCHED THEN.
Cualquier departamento de Departments que no exista en la tabla de origen Departments_delta se elimina de Departments. Esto se lleva a cabo en la cláusula WHEN NOT MATCHED BY SOURCE THEN.
MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
inserted.Manager AS SourceManager,
deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
deleted.Manager AS TargetManager;
Vea también