MERGE (Transact-SQL)
Se aplica a: SQL Server
Azure SQL Database
Azure SQL Instancia administrada
Azure Synapse Analytics
La instrucción MERGE inserta, actualiza o elimina operaciones en una tabla de destino a partir de los resultados de una combinación con una tabla de origen. Por ejemplo, sincronice dos tablas mediante la inserción, actualización o eliminación de las filas de una tabla según las diferencias que se encuentren en la otra.
Nota:
Para obtener información de MERGE específica de Azure Synapse Analytics, cambie la selección de versión a Azure Synapse Analytics.
Nota:
MERGE ahora está disponible con carácter general en el grupo de SQL dedicado de Synapse con la versión 10.0.17829.0 o superior. Conexión a un grupo de SQL dedicado (anteriormente SQL DW) y ejecución SELECT @@VERSION
. Se puede requerir una pausa y reanudación para asegurarse de que la instancia obtiene la versión más reciente.
Sugerencia
el comportamiento condicional descrito para la instrucción MERGE funciona mejor cuando las dos tablas tienen una mezcla compleja de características coincidentes. Por ejemplo, insertar una fila si no existe o actualizar una fila si coincide. Cuando simplemente se actualiza una tabla basada en las filas de otra tabla, mejore el rendimiento y la escalabilidad con las instrucciones INSERT, UPDATE y DELETE. Por ejemplo:
INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);
Convenciones de sintaxis de Transact-SQL
Sintaxis
Sintaxis de SQL Server y Azure SQL Database:
[ WITH <common_table_expression> [,...n] ]
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
USING <table_source> [ [ AS ] table_alias ]
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;
<target_table> ::=
{
[ database_name . schema_name . | schema_name . ] [ [ AS ] target_table ]
| @variable [ [ AS ] target_table ]
| common_table_expression_name [ [ AS ] target_table ]
}
<merge_hint>::=
{
{ [ <table_hint_limited> [ ,...n ] ]
[ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
}
}
<merge_search_condition> ::=
<search_condition>
<merge_matched>::=
{ UPDATE SET <set_clause> | DELETE }
<merge_not_matched>::=
{
INSERT [ ( column_list ) ]
{ VALUES ( values_list )
| DEFAULT VALUES }
}
<clause_search_condition> ::=
<search_condition>
Nota:
Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.
Sintaxis para Azure Synapse Analytics:
[ WITH <common_table_expression> [,...n] ]
MERGE
[ INTO ] <target_table> [ [ AS ] table_alias ]
USING <table_source> [ [ AS ] table_alias ]
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ] [ ...n ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
; -- The semi-colon is required, or the query will return a syntax error.
<target_table> ::=
{
[ database_name . schema_name . | schema_name . ]
target_table
}
<merge_search_condition> ::=
<search_condition>
<merge_matched>::=
{ UPDATE SET <set_clause> | DELETE }
<merge_not_matched>::=
{
INSERT [ ( column_list ) ]
VALUES ( values_list )
}
<clause_search_condition> ::=
<search_condition>
Argumentos
WITH <common_table_expression>
Especifica la vista o el conjunto de resultados temporal indicado, que también se conoce como expresión de tabla común, definido en el ámbito de la instrucción MERGE. El conjunto de resultados deriva de una consulta simple. La instrucción MERGE hace referencia al conjunto de resultados. Para más información, consulte WITH common_table_expression (Transact-SQL).
TOP ( expression ) [ PERCENT ]
Especifica el número o porcentaje de filas afectadas. expression puede ser un número o un porcentaje de las filas. Las filas a las que se hace referencia en la expresión TOP no están organizadas en ningún orden. Para obtener más información, vea TOP (Transact-SQL).
La cláusula TOP se aplica después de que se combinen toda la tabla de origen y toda la tabla de destino, y se quiten las filas combinadas que no reúnan las condiciones para las acciones de inserción, actualización o eliminación. La cláusula TOP reduce aún más el número de filas unidas al valor especificado. Estas acciones (inserción, actualización o eliminación) se aplican a las filas unidas restantes de forma desordenada. Es decir, no hay ningún orden en el que las filas se distribuyan entre las acciones definidas en las cláusulas WHEN. Por ejemplo, si se especifica TOP (10), afectará a 10 filas. De estas filas, 7 se pueden actualizar y 3 insertar, o se puede eliminar 1, actualizar 5 e insertar 4, y así sucesivamente.
Sin filtros en la tabla de origen, la instrucción MERGE puede realizar un recorrido de tabla o un examen de índice agrupado en la tabla de origen, así como un recorrido de tabla o un examen de índice agrupado de la tabla de destino. Por lo tanto, el rendimiento E/S se ve afectado incluso cuando se usa la cláusula TOP para modificar una tabla grande al crear múltiples lotes. En este escenario, es importante asegurarse de que todos los lotes sucesivos tengan como destino nuevas filas.
database_name
El nombre de la base de datos donde se encuentra target_table.
schema_name
El nombre del esquema al que pertenece target_table.
target_table
La tabla o vista con la que se hacen coincidir las filas de datos de <table_source>
según <clause_search_condition>
. target_table es el destino de las operaciones de inserción, actualización o eliminación especificado por las cláusulas WHEN de la instrucción MERGE.
Si target_table es una vista, cualquier acción con ella debe satisfacer las condiciones para actualizar las vistas. Para más información, vea Modificar datos mediante una vista.
target_table no puede ser una tabla remota. target_table no puede tener ninguna regla definida. target_table no puede ser una tabla optimizada para memoria.
Las sugerencias se pueden especificar como <merge_hint>
.
<merge_hint>
no es compatible con Azure Synapse Analytics.
[ AS ] table_alias
Nombre alternativo para hacer referencia a una tabla para target_table.
USING <table_source>
Especifica el origen de datos que se hace coincidir con las filas de datos de target_table en función de <merge_search_condition>
. El resultado de esta coincidencia dicta las acciones que tomarán las cláusulas WHEN de la instrucción MERGE. <table_source>
puede ser una tabla remota o una tabla derivada que acceda a las tablas remotas.
<table_source>
puede ser una tabla derivada que use el constructor con valores de tabla de Transact-SQL para construir una tabla especificando varias filas.
<table_source>
puede ser una tabla derivada que use SELECT ... UNION ALL
para construir una tabla mediante la especificación de varias filas.
[ AS ] table_alias
Nombre alternativo para hacer referencia a una tabla para table_source.
Para obtener más información sobre la sintaxis y los argumentos de esta cláusula, vea FROM (Transact-SQL).
ON <merge_search_condition>
Especifica las condiciones en las que <table_source>
se combina con target_table para determinar dónde coinciden.
Precaución
Es importante especificar solamente las columnas de la tabla de destino que se utilizan para los propósitos de la coincidencia. Es decir, especifique las columnas de la tabla de destino que se comparan con la correspondiente columna de la tabla de origen. No intente mejorar el rendimiento de las consultas filtrando las filas de la tabla de destino en la cláusula ON; por ejemplo, según se especifica con AND NOT target_table.column_x = value
. Si se hace esto, se pueden devolver resultados inesperados e incorrectos.
WHEN MATCHED THEN <merge_matched>
Especifica que todas las filas de *target_table que coinciden con las filas devueltas por <table_source>
ON <merge_search_condition>
y que satisfacen alguna condición de búsqueda adicional se actualizan o eliminan según la cláusula <merge_matched>
.
La instrucción MERGE puede tener, a lo sumo, dos cláusulas WHEN MATCHED. Si se especifican dos cláusulas, la primera debe ir acompañada de una cláusula AND <search_condition>
. Para una fila determinada, la segunda cláusula WHEN MATCHED se aplica solamente si no se aplica la primera. Si hay dos cláusulas WHEN MATCHED, una debe especificar una acción UPDATE y la otra una acción DELETE. Si se especifica UPDATE en la cláusula <merge_matched>
y más de una fila de <table_source>
coincide con una de target_table según <merge_search_condition>
, SQL Server devuelve un error. La instrucción MERGE no puede actualizar la misma fila más de una vez, ni actualizar o eliminar la misma fila.
WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
Especifica que una fila se inserta en target_table para cada fila devuelta por <table_source>
ON <merge_search_condition>
que no coincide con una fila de target_table, pero satisface una condición de búsqueda adicional, si está presente. La cláusula <merge_not_matched>
especifica los valores que se van a insertar. La instrucción MERGE solo puede tener una cláusula WHEN NOT MATCHED [ BY TARGET ].
WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
Especifica que todas las filas de *target_table que no coinciden con las filas devueltas por <table_source>
ON <merge_search_condition>
y que satisfacen alguna condición de búsqueda adicional se actualizan o eliminan según la cláusula <merge_matched>
.
La instrucción MERGE puede tener a lo sumo dos cláusulas WHEN NOT MATCHED BY SOURCE. Si se especifican dos cláusulas, la primera debe ir acompañada de una cláusula AND <clause_search_condition>
. Para una fila determinada, la segunda cláusula WHEN NOT MATCHED BY SOURCE se aplica solamente si no se aplica la primera. Si hay dos cláusulas WHEN NOT MATCHED BY SOURCE, una debe especificar una acción UPDATE y la otra una acción DELETE. En <clause_search_condition>
solo se puede hacer referencia a las columnas de la tabla de destino.
Cuando <table_source>
no devuelve ninguna fila, no se puede acceder a las columnas de la tabla de origen. Si la acción de actualización o eliminación especificada en la cláusula <merge_matched>
hace referencia a las columnas de la tabla de origen, se devuelve el error 207 (nombre de columna no válido). Por ejemplo, la cláusula WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1
puede hacer que la instrucción genere un error porque Col1
en la tabla de origen es inaccesible.
AND <clause_search_condition>
Especifica cualquier condición de búsqueda válida. Para obtener más información, vea Condición de búsqueda (Transact-SQL).
<table_hint_limited>
Especifica una o más sugerencias de tabla que se aplican en la tabla de destino para cada una de las acciones de inserción, actualización o eliminación que realiza la instrucción MERGE. La palabra clave WITH y los paréntesis son obligatorios.
No se permiten NOLOCK ni READUNCOMMITTED. Para obtener más información sobre las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).
Especificar la sugerencia TABLOCK en una tabla que es el destino de una instrucción INSERT tiene el mismo efecto que especificar la sugerencia TABLOCKX. Se realiza un bloqueo exclusivo en la tabla. Cuando se especifica FORCESEEK, se aplica a la instancia implícita de la tabla de destino combinada con la tabla de origen.
Precaución
Si se especifica READPAST con WHEN NOT MATCHED [ BY TARGET ] THEN INSERT, pueden producirse operaciones INSERT que infrinjan las restricciones UNIQUE.
INDEX ( index_val [ ,...n ] )
Especifica el nombre o identificador de uno o más índices de la tabla de destino para realizar una combinación implícita con la tabla de origen. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).
<output_clause>
Devuelve una fila para cada fila de target_table que se actualiza, inserta o elimina, sin seguir ningún orden concreto. $action
se puede especificar en la cláusula de salida. $action
es una columna de tipo nvarchar(10) que devuelve uno de estos tres valores para cada fila: INSERT
, UPDATE
, o DELETE
, según la acción realizada en esa fila. La cláusula OUTPUT es la manera recomendada de consultar o contar las filas afectadas por MERGE. Para obtener más información sobre los argumentos y el comportamiento de esta cláusula, vea Cláusula OUTPUT (Transact-SQL).
OPTION ( <query_hint> [ ,...n ] )
Especifica que se utilizan las sugerencias del optimizador para personalizar el modo en que el Motor de base de datos procesa la instrucción. Para obtener más información, vea Sugerencias (Transact-SQL) - Consulta.
<merge_matched>
Especifica la acción de actualización o eliminación que se aplica a todas las filas de target_table que no coinciden con las filas devueltas por <table_source>
ON <merge_search_condition>
y que satisfacen cualquier condición de búsqueda adicional.
UPDATE SET <set_clause>
Especifica la lista de nombres de columna o de variable que se van a actualizar en la tabla de destino y los valores con los que se actualizan.
Para obtener más información sobre la sintaxis y los argumentos de esta cláusula, vea UPDATE (Transact-SQL). No se admite el establecimiento de una variable con el mismo valor que una columna.
Delete
Especifica que se eliminarán las filas que coincidan con las filas de target_table.
<merge_not_matched>
Especifica los valores que insertar en la tabla de destino.
( column_list )
Una lista de una o varias columnas de la tabla de destino en la que insertar los datos. Las columnas se deben especificar como un nombre de una sola parte o, de lo contrario, se produce un error en la instrucción MERGE. column_list debe ir entre paréntesis y delimitada con comas.
VALUES ( values_list)
Una lista separada por comas de constantes, variables o expresiones que devuelve los valores que se insertarán en la tabla de destino. Las expresiones no pueden contener una instrucción EXECUTE.
DEFAULT VALUES
Hace que la fila insertada contenga los valores predeterminados definidos para cada columna.
Para obtener más información sobre esta cláusula, vea INSERT (Transact-SQL).
<search_condition>
Especifica las condiciones de búsqueda para especificar <merge_search_condition>
o <clause_search_condition>
. Para obtener más información sobre los argumentos de esta cláusula, vea Condiciones de búsqueda (Transact-SQL).
<graph search pattern>
Especifica el patrón de coincidencia de gráficos. Para obtener más información sobre los argumentos de esta cláusula, vea MATCH (Transact-SQL).
Comentarios
Al menos se debe especificar una de las tres cláusulas MATCHED, pero se pueden especificar en cualquier orden. Una variable no puede actualizarse más de una vez en la misma cláusula MATCHED.
Cualquier acción de inserción, actualización o eliminación especificada en la tabla de destino por la instrucción MERGE está limitada por las restricciones definidas en ella, incluidas las restricciones de integridad referencial en cascada. Si IGNORE_DUP_KEY es ON para algún índice único de la tabla de destino, MERGE omite este valor.
La instrucción MERGE requiere un punto y coma (;) como terminador. Se genera el error 10713 cuando una instrucción MERGE se ejecuta sin el terminador.
Cuando se usa después de MERGE, @@ROWCOUNT (Transact-SQL) devuelve al cliente el número total de filas insertadas, actualizadas y eliminadas.
MERGE es una palabra clave totalmente reservada cuando el nivel de compatibilidad de la base de datos se establece en 100
o superior. La instrucción MERGE también está disponible en los niveles de compatibilidad 90
y 100
de la base de datos; sin embargo, la palabra clave no se reserva completamente cuando el nivel de compatibilidad de la base de datos se establece en 90
.
Precaución
No utilice la instrucción MERGE cuando se usa la replicación de actualización en cola. MERGE y el desencadenador de actualización en cola no son compatibles. Reemplace la instrucción MERGE con una instrucción de inserción o de actualización.
Consideraciones sobre Azure Synapse Analytics
En Azure Synapse Analytics, el comando MERGE tiene las siguientes diferencias en comparación con SQL Server y Azure SQL Database.
- No se admite el uso de MERGE para actualizar una columna de claves de distribución en compilaciones anteriores a 10.0.17829.0. Si no se puede pausar o forzar la actualización, use la instrucción ANSI
UPDATE FROM ... JOIN
como solución alternativa hasta la versión 10.0.17829.0. - Una actualización MERGE se implementa como un par de eliminación e inserción. El recuento de filas afectado de una actualización MERGE incluye las filas eliminadas e insertadas.
MERGE...WHEN NOT MATCHED INSERT
no se admite para tablas con columnas IDENTITY.- El constructor de valores de tabla no se puede usar en la cláusula USING para la tabla de origen. Use
SELECT ... UNION ALL
para crear una tabla de origen derivada con varias filas. - En esta tabla se describe la compatibilidad de las tablas con distintos tipos de distribución:
MERGE CLAUSE en Azure Synapse Analytics | Tabla de distribución TARGET admitida | Tabla de distribución SOURCE admitida | Comentario |
---|---|---|---|
WHEN MATCHED |
Todos los tipos de distribución | Todos los tipos de distribución | |
NOT MATCHED BY TARGET |
HASH | Todos los tipos de distribución | Use UPDATE /DELETE FROM...JOIN para sincronizar dos tablas. |
NOT MATCHED BY SOURCE |
Todos los tipos de distribución | Todos los tipos de distribución |
Sugerencia
Si usa la clave hash de distribución como la columna JOIN en MERGE y realiza solo una comparación de igualdad, puede omitir la clave de distribución de la lista de columnas en la cláusula WHEN MATCHED THEN UPDATE SET
, ya que se trata de una actualización redundante.
En Azure Synapse Analytics, el comando MERGE, en compilaciones anteriores a 10.0.17829.0 puede, en determinadas condiciones, dejar la tabla de destino en un estado incoherente con filas colocadas en la distribución equivocada, lo que hace que las consultas posteriores devuelvan, en algunos casos, resultados incorrectos. Este problema puede producirse en dos casos:
Escenario | Comentario |
---|---|
Caso 1 Uso de MERGE en una tabla TARGET con distribución de HASH que contiene índices secundarios o una restricción UNIQUE. |
- Se corrigió en Synapse SQL versión 10.0.15563.0 y posteriores. - Si SELECT @@VERSION devuelve una versión inferior a la versión 10.0.15563.0, pause y reanude manualmente el grupo de Synapse SQL para seleccionar esta corrección.- Hasta que se haya aplicado la corrección al grupo de Synapse SQL, evite usar el comando MERGE en las tablas TARGET con distribución de HASH que tienen índices secundarios o restricciones UNIQUE. |
Caso 2 Uso de MERGE para actualizar una columna de clave de distribución de una tabla con distribución de HASH. |
- Se corrigió en Synapse SQL versión10.0.17829.0 y posteriores. - Si SELECT @@VERSION devuelve una versión inferior a la versión 10.0.17829.0, pause y reanude manualmente el grupo de Synapse SQL para seleccionar esta corrección.- Hasta que se haya aplicado la corrección al grupo de Synapse SQL, evite usar el comando MERGE para actualizar las columnas de clave de distribución. |
Las actualizaciones en ambos escenarios no reparan las tablas ya afectadas por la ejecución anterior de MERGE. Use los scripts siguientes para identificar y reparar manualmente las tablas afectadas.
Para comprobar qué tablas distribuidas de HASH de una base de datos pueden suponer un problema (si se usan en los casos anteriores), ejecute esta instrucción:
-- Case 1
SELECT a.name,
c.distribution_policy_desc,
b.type
FROM sys.tables a
INNER JOIN sys.indexes b
ON a.object_id = b.object_id
INNER JOIN sys.pdw_table_distribution_properties c
ON a.object_id = c.object_id
WHERE b.type = 2
AND c.distribution_policy_desc = 'HASH';
-- Subject to Case 2, if distribution key value is updated in MERGE statement
SELECT a.name,
c.distribution_policy_desc
FROM sys.tables a
INNER JOIN sys.pdw_table_distribution_properties c
ON a.object_id = c.object_id
WHERE c.distribution_policy_desc = 'HASH';
Para saber si una tabla distribuidas de HASH para MERGE se va a ver afectada por los casos 1 o 2, siga estos pasos para examinar si las tablas tienen filas descargadas en una distribución incorrecta. Si no need for repair
se devuelve, esta tabla no se ve afectada.
IF object_id('[check_table_1]', 'U') IS NOT NULL
DROP TABLE [check_table_1]
GO
IF object_id('[check_table_2]', 'U') IS NOT NULL
DROP TABLE [check_table_2]
GO
CREATE TABLE [check_table_1]
WITH (DISTRIBUTION = ROUND_ROBIN) AS
SELECT <DISTRIBUTION_COLUMN> AS x
FROM <MERGE_TABLE>
GROUP BY <DISTRIBUTION_COLUMN>;
GO
CREATE TABLE [check_table_2]
WITH (DISTRIBUTION = HASH (x)) AS
SELECT x
FROM [check_table_1];
GO
IF NOT EXISTS (
SELECT TOP 1 *
FROM (
SELECT <DISTRIBUTION_COLUMN> AS x
FROM <MERGE_TABLE>
EXCEPT
SELECT x
FROM [check_table_2]
) AS tmp
)
SELECT 'no need for repair' AS result
ELSE
SELECT 'needs repair' AS result
GO
IF object_id('[check_table_1]', 'U') IS NOT NULL
DROP TABLE [check_table_1]
GO
IF object_id('[check_table_2]', 'U') IS NOT NULL
DROP TABLE [check_table_2]
GO
Para reparar las tablas afectadas, ejecute estas instrucciones para copiar todas las filas de la tabla antigua en una nueva tabla.
IF object_id('[repair_table_temp]', 'U') IS NOT NULL
DROP TABLE [repair_table_temp];
GO
IF object_id('[repair_table]', 'U') IS NOT NULL
DROP TABLE [repair_table];
GO
CREATE TABLE [repair_table_temp]
WITH (DISTRIBUTION = ROUND_ROBIN) AS
SELECT *
FROM <MERGE_TABLE>;
GO
-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
CREATE TABLE [repair_table]
WITH (DISTRIBUTION = HASH (<DISTRIBUTION_COLUMN>)) AS
SELECT *
FROM [repair_table_temp];
GO
IF object_id('[repair_table_temp]', 'U') IS NOT NULL
DROP TABLE [repair_table_temp];
GO
Solución de problemas
En determinados escenarios, una instrucción MERGE puede producir el error CREATE TABLE failed because column <> in table <> exceeds the maximum of 1024 columns.
, incluso cuando ni la tabla objetivo ni origen tienen 1024 columnas. Este escenario puede surgir cuando se cumplen todas las condiciones siguientes:
- Se especifican varias columnas en una operación DELETE, UPDATE SET o INSERT dentro de MERGE (no es específica de ninguna cláusula WHEN [NOT] MATCHED)
- Cualquier columna de la condición JOIN tiene un índice no agrupado (NCI)
- La tabla de destino está distribuida por HASH
Si se encuentra este error, las soluciones alternativas sugeridas son las siguientes:
- Quitar el índice no agrupado (NCI) de las columnas JOIN o combinar en columnas sin una NCI. Si posteriormente actualiza las tablas subyacentes para incluir una NCI en las columnas JOIN, la instrucción MERGE puede ser susceptible a este error en tiempo de ejecución. Para obtener más información, vea DROP INDEX.
- Use las instrucciones UPDATE, DELETE e INSERT en lugar de MERGE.
Implementación de desencadenadores
Para cada acción de inserción, actualización o eliminación especificada en la instrucción MERGE, SQL Server activa los desencadenadores AFTER correspondientes definidos en la tabla de destino, pero no garantiza qué acción activará los desencadenadores primero o último. Los desencadenadores definidos para la misma acción cumplen el orden que especifique. Para más información sobre cómo establecer el orden de activación de los desencadenadores, vea Especificar el primer y el último desencadenador.
Si la tabla de destino tiene habilitado un desencadenador INSTEAD OF definido en ella para una acción de inserción, actualización o eliminación realizada por una instrucción MERGE, debe tener habilitado un desencadenador INSTEAD OF para todas las acciones especificadas en la instrucción MERGE.
Si se ha definido un desencadenador INSTEAD OF UPDATE o INSTEAD OF DELETE en target_table, las operaciones de actualización o eliminación no se ejecutan. En su lugar, se activan los desencadenadores y las tablas inserted y deleted se rellenan en consecuencia.
Si se definen desencadenadores INSTEAD OF INSERT en target_table, la operación de inserción no se realiza. En su lugar, la tabla se rellena en consecuencia.
Nota:
A diferencia de las instrucciones INSERT, UPDATE y DELETE independientes, el número de filas reflejadas por @@ROWCOUNT dentro de un desencadenador puede ser mayor. El @@ROWCOUNT dentro de cualquier desencadenador AFTER (independientemente de las instrucciones de modificación de datos que capture el desencadenador) reflejará el número total de filas afectadas por MERGE. Por ejemplo, si una instrucción MERGE inserta una fila, actualiza una fila y elimina una fila, @@ROWCOUNT será tres para cualquier desencadenador AFTER, incluso si el desencadenador solo se declara para instrucciones INSERT.
Permisos
Requiere el permiso SELECT en la tabla de origen y los permisos INSERT, UPDATE o DELETE en la tabla de destino. Para más información, consulte la sección Permisos de los artículos SELECT, INSERT, UPDATE y DELETE.
Procedimientos recomendados para índices
Mediante la instrucción MERGE, puede reemplazar las instrucciones DML individuales con una instrucción única. Esto puede mejorar el rendimiento de las consultas debido a que las operaciones se realizan dentro de una instrucción única y, por consiguiente, se reduce el número de veces que se procesan los datos en las tablas de destino y de origen. Sin embargo, las mejoras en el rendimiento dependerán de si hay índices, combinaciones y otras consideraciones correctas en su lugar.
Para mejorar el rendimiento de la instrucción MERGE, recomendamos las siguientes instrucciones de índices:
- Cree índices para facilitar la combinación entre el origen y el destino de MERGE:
- Cree un índice en las columnas de combinación de la tabla de origen que tiene claves que cubren la lógica de combinación de la tabla de destino. Si es posible, debe ser único.
- Además, cree un índice en las columnas de combinación de la tabla de destino. Si es posible, debe ser un índice agrupado único.
- Estos dos índices garantizan que los datos de las tablas están ordenados y la singularidad ayuda al rendimiento de la comparación. Se mejora el rendimiento de las consultas debido a que el optimizador de consultas no necesita realizar un procesamiento de validación adicional para buscar y actualizar filas duplicadas, por lo que no son necesarias operaciones de ordenación adicionales.
- Evite tablas con cualquier tipo de índice de almacén de columnas como destino de instrucciones MERGE. Al igual que con cualquier UPDATE, puede encontrar un mejor rendimiento con los índices de almacén de columnas mediante la actualización de una tabla de almacén de filas por fases y, a continuación, la realización de una operación DELETE e INSERT por lotes, en lugar de UPDATE o MERGE.
Consideraciones de simultaneidad para MERGE
En términos de bloqueo, MERGE es diferente de las instrucciones discretas y consecutivas INSERT, UPDATE y DELETE. MERGE todavía ejecuta las operaciones INSERT, UPDATE y DELETE, pero usa mecanismos de bloqueo diferentes. Puede ser más eficiente escribir instrucciones INSERT, UPDATE y DELETE discretas para algunas necesidades de la aplicación. A gran escala, MERGE puede presentar problemas de simultaneidad complicados o requerir una solución de problemas avanzada. Por lo tanto, pruebe exhaustivamente cualquier instrucción MERGE antes de implementarla en producción.
Las instrucciones MERGE son un reemplazo adecuado para las operaciones discretas INSERT, UPDATE y DELETE en los escenarios siguientes (entre otros):
- Operaciones ETL que implican recuentos de filas grandes que se ejecutan durante un tiempo en el que no* se esperan otras operaciones simultáneas. Cuando se espera una simultaneidad intensa, la lógica INSERT, UPDATE y DELETE puede tener un mejor rendimiento por separado, con menos bloqueo, que una instrucción MERGE.
- Es poco probable que las operaciones complejas que implican recuentos de filas y transacciones pequeñas se ejecuten durante más tiempo.
- Operaciones complejas que implican tablas de usuario en las que los índices se pueden diseñar para garantizar planes de ejecución óptimos, evitando búsquedas y recorridos de tablas en favor de exámenes de índice o, idealmente, búsquedas de índices.
Otras consideraciones para la simultaneidad:
- En algunos escenarios en los que se espera que MERGE inserte y actualice las claves únicas, la especificación de HOLDLOCK evitará infracciones de clave única. HOLDLOCK es un sinónimo del nivel de aislamiento de transacción SERIALIZABLE, que no permite que otras transacciones simultáneas modifiquen los datos que esta transacción ha leído. SERIALIZABLE es el nivel de aislamiento más seguro, pero proporciona la menor simultaneidad con otras transacciones que mantienen los bloqueos en intervalos de datos para evitar que las filas fantasma se inserten o actualicen mientras haya lecturas en curso. Para obtener más información sobre HOLDLOCK, vea Sugerencias y SET TRANSACTION ISOLATION LEVEL (Transact-SQL).
Procedimientos recomendados para JOIN
Para mejorar el rendimiento de la instrucción MERGE y asegurarse de que se obtienen los resultados correctos, recomendamos las siguientes instrucciones de combinación:
- 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 con otros valores como, por ejemplo, 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 la 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, consulte Modificar datos mediante una vista.
- Use la cláusula
WITH <common table expression>
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.
La operación de combinación en la instrucción MERGE se optimiza de la misma manera que una combinación en una instrucción SELECT. Es decir, cuando SQL Server procesa combinaciones, el optimizador de consultas elige el método más eficaz entre varias posibilidades para procesar la combinación. Cuando el origen y el destino son de tamaño similar y las instrucciones de índice descritas anteriormente se aplican a las tablas de destino y de origen, el plan de consulta más eficaz es un operador de combinación de mezcla. Esto se debe a que ambas tablas se examinan una vez y no hay necesidad de ordenar los datos. Cuando el origen es menor que la tabla de destino, es preferible usar un operador de bucles anidados.
Puede exigir el uso de una combinación concreta especificando la cláusula OPTION (<query_hint>)
en la instrucción MERGE. Se recomienda no usar la combinación hash como una sugerencia de consulta para las instrucciones MERGE, porque este tipo de combinación no usa índices.
Procedimientos recomendados para parametrización
Si una instrucción SELECT, INSERT, UPDATE o DELETE se ejecuta sin parámetros, el optimizador de consultas de SQL Server puede decidir parametrizar la instrucción internamente. Esto indica que todos los valores literales incluidos en la consulta se sustituirán por parámetros. Por ejemplo, la instrucción INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)
se puede implementar internamente como INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2)
. Este proceso, denominado parametrización simple, aumenta la capacidad del motor relacional para hacer coincidir nuevas instrucciones SQL con los planes de ejecución existentes compilados previamente. Se puede mejorar el rendimiento de las consultas debido a que se reduce la frecuencia de las compilaciones y recompilaciones de la consulta. El optimizador de consultas no aplica el proceso de parametrización simple a las instrucciones MERGE. Por consiguiente, puede que no se realicen las instrucciones MERGE que contienen valores literales, además de las instrucciones INSERT, DELETE o UPDATE individuales, porque se compila un plan nuevo cada vez que se ejecuta la instrucción MERGE.
Para mejorar el rendimiento de las consultas, recomendamos las siguientes instrucciones de parametrización:
- Parametrice todos los valores literales en la cláusula
ON <merge_search_condition>
y en las cláusulasWHEN
de la instrucción MERGE. Por ejemplo, puede incorporar la instrucción MERGE en un procedimiento almacenado que reemplaza los valores literales con parámetros de entrada adecuados. - Si no puede parametrizar la instrucción, cree una guía de plan de tipo
TEMPLATE
y especifique la sugerencia de consultaPARAMETERIZATION FORCED
en la guía de plan. Para más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan. - Si las instrucciones MERGE se ejecutan con frecuencia en la base de datos, considere la posibilidad de establecer en FORCED la opción PARAMETERIZATION en la base de datos. Actúe con precaución cuando establezca esta opción. La opción
PARAMETERIZATION
es un valor de nivel de base de datos y afecta a la manera en que se procesan todas las consultas a la base de datos. Para obtener más información, vea Parametrización forzada. - Como alternativa más reciente y sencilla a las guías de plan, considere una estrategia similar con Sugerencias del almacén de consultas. Para obtener más información, vea Sugerencias del Almacén de consultas.
Procedimientos recomendados para la cláusula TOP
En la instrucción MERGE la cláusula TOP especifica el número o porcentaje de filas afectadas después de la combinación de la tabla de origen y la tabla de destino, y después de quitar las filas que no cumplen los requisitos para una acción de inserción, actualización o eliminación. La cláusula TOP reduce aún más el número de filas combinadas al valor especificado y se aplican las acciones de inserción, actualización o eliminación a las filas combinadas restantes de una manera desordenada. Es decir, no hay ningún orden en el que las filas se distribuyan entre las acciones definidas en las cláusulas WHEN. Por ejemplo, cuando se especifica TOP (10) afecta a 10 filas; de estas filas, 7 se pueden actualizar y 3 insertar, o se pueden eliminar 1, actualizar 5 e insertar 4, etc.
Es habitual usar la cláusula TOP para realizar operaciones del lenguaje de manipulación de datos (DML) en una tabla grande en lotes. Cuando se usa la cláusula TOP en la instrucción MERGE con este fin, es importante comprender las implicaciones siguientes.
Puede afectar al rendimiento de la E/S.
La instrucción MERGE realiza exámenes de tabla completos, tanto de las tablas de destino como de origen. Al dividir la operación en lotes, se reduce el número de operaciones de escritura realizadas por lote; sin embargo, cada lote realiza exámenes de tabla completos, tanto de las tablas de destino como de origen. La actividad de lectura resultante puede afectar al rendimiento de la consulta y a otra actividad simultánea en las tablas.
Se pueden producir resultados incorrectos.
Es importante asegurarse de que todos los lotes sucesivos se destinen a filas nuevas, o podría producirse un comportamiento no deseado como la inserción incorrecta de filas duplicadas en la tabla de destino. Esto puede ocurrir cuando la tabla de origen incluye una fila que no estaba en un lote de destino pero estaba en la tabla de destino total. Para asegurar que los resultados son correctos:
- Use la cláusula ON para determinar qué filas de origen afectan a las filas de destino existentes y cuáles son auténticamente nuevas.
- Use una condición adicional en la cláusula WHEN MATCHED para determinar si un lote anterior ya se actualizó por la fila de destino.
- Use una condición adicional en la cláusula WHEN MATCHED y la lógica SET para comprobar que la misma fila no se puede actualizar dos veces.
Dado que la cláusula TOP solo se aplica una vez aplicadas estas cláusulas, cada ejecución inserta una fila no coincidente inigualable o actualiza una fila existente.
Procedimientos recomendados para la carga masiva
La instrucción MERGE se puede usar para cargar eficazmente datos de manera masiva del archivo de datos de origen en una tabla de destino especificando la cláusula OPENROWSET(BULK...)
como el origen de la tabla. De esta forma, el archivo completo se procesa en un lote único.
Para mejorar el rendimiento del proceso de mezcla masiva, recomendamos las siguientes instrucciones:
Cree un índice clúster en las columnas de combinación de la tabla de destino.
Deshabilite otros índices no agrupados y no únicos en la tabla de destino mientras dure la carga masiva MERGE y habilítelos posteriormente. Esto es común y útil para las operaciones de datos masivas nocturnas.
Use las sugerencias ORDER y UNIQUE en la cláusula
OPENROWSET(BULK...)
para especificar cómo se ordena el archivo de datos de origen.De forma predeterminada, la operación masiva presupone que los datos del archivo no están ordenados. Por consiguiente, es importante que los datos del origen estén ordenados según el índice agrupado de la tabla de destino y que la sugerencia ORDER se use para indicar el orden, de manera que el optimizador de consultas pueda generar un plan de consultas más eficaz. Las sugerencias se validan en tiempo de ejecución; si el flujo de datos no se ajusta a las sugerencias especificadas, se inicia un error.
Estas instrucciones garantizan que las claves de unión son únicas y que el criterio de ordenación de los datos en el archivo de origen coincide con la tabla de destino. Se mejora el rendimiento de las consultas debido a que las operaciones de ordenación adicionales no son necesarias y no se requieren copias innecesarias de datos.
Medición y diagnóstico del rendimiento de MERGE
Las características siguientes están disponibles para ayudarle a medir y diagnosticar el rendimiento de las instrucciones MERGE.
- Use el contador merge stmt en la vista de administración dinámica sys.dm_exec_query_optimizer_info para devolver el número de optimizaciones de consulta para las instrucciones MERGE.
- Use el atributo
merge_action_type
en la vista de administración dinámica sys.dm_exec_plan_attributes para devolver el tipo de plan de ejecución de desencadenadores que se usa como el resultado de una instrucción MERGE. - Use Sesión de eventos extendida para recopilar datos de la solución de problemas de la instrucción MERGE de la misma manera que haría para otras instrucciones del lenguaje de manipulación de datos (DML). Para más información sobre los Información general sobre eventos extendidos, vea Inicio rápido: Eventos extendidos y Uso de perfiles XEvent de SSMS.
Ejemplos
A Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla en una sola instrucción
Un escenario común es la actualización de una o más columnas de una tabla si existe una línea coincidente. O bien, con la inserción de los datos como una nueva fila si no existe una fila coincidente. Normalmente, se realiza cualquiera de los dos escenarios mediante el paso de los parámetros a un procedimiento almacenado que contiene las instrucciones INSERT y UPDATE adecuadas. Con la instrucción MERGE puede realizar ambas tareas en una sola instrucción. En el ejemplo siguiente se muestra un procedimiento almacenado de la base de datos AdventureWorks2022 que contiene una instrucción INSERT y una instrucción UPDATE. A continuación, el procedimiento se modifica para ejecutar las operaciones equivalentes utilizando una sola instrucción MERGE.
CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
SET NOCOUNT ON;
-- Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO Production.UnitMeasure (
UnitMeasureCode,
Name
)
VALUES (@UnitMeasureCode, @Name)
END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO
-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
ExistingCode NCHAR(3),
ExistingName NVARCHAR(50),
ExistingDate DATETIME,
ActionTaken NVARCHAR(10),
NewCode NCHAR(3),
NewName NVARCHAR(50),
NewDate DATETIME
);
GO
ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
@Name NVARCHAR(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS tgt
USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
WHEN MATCHED
THEN
UPDATE
SET Name = src.Name
WHEN NOT MATCHED
THEN
INSERT (UnitMeasureCode, Name)
VALUES (src.UnitMeasureCode, src.Name)
OUTPUT deleted.*,
$action,
inserted.*
INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';
SELECT * FROM #MyTempTable;
-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');
DROP TABLE #MyTempTable;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
@Name NVARCHAR(25)
AS
BEGIN
SET NOCOUNT ON;
-- Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0)
BEGIN
INSERT INTO Production.UnitMeasure (
UnitMeasureCode,
Name
)
VALUES (@UnitMeasureCode, @Name)
END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO
-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
@Name NVARCHAR(25)
AS
BEGIN
SET NOCOUNT ON;
MERGE Production.UnitMeasure AS tgt
USING (
SELECT @UnitMeasureCode,
@Name
) AS src(UnitMeasureCode, Name)
ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
WHEN MATCHED
THEN
UPDATE SET Name = src.Name
WHEN NOT MATCHED
THEN
INSERT (UnitMeasureCode, Name)
VALUES (src.UnitMeasureCode, src.Name);
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';
-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');
GO
B. Usar MERGE para realizar operaciones UPDATE y DELETE en una tabla en una sola instrucción
En el ejemplo siguiente se usa MERGE para actualizar a diario la tabla ProductInventory
de la base de datos de ejemplo AdventureWorks2022, en función de los pedidos procesados en la tabla SalesOrderDetail
. La columna Quantity
de la tabla ProductInventory
se actualiza restando el número de pedidos realizados cada día para cada producto de la tabla SalesOrderDetail
. Si el número de pedidos de un producto baja el nivel de inventario del mismo hasta 0 o un valor menor, la fila correspondiente a ese producto se elimina de la tabla ProductInventory
.
CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
SELECT ProductID,
SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID
) AS src(ProductID, OrderQty)
ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
AND tgt.Quantity - src.OrderQty <= 0
THEN
DELETE
WHEN MATCHED
THEN
UPDATE
SET tgt.Quantity = tgt.Quantity - src.OrderQty,
tgt.ModifiedDate = GETDATE()
OUTPUT $action,
Inserted.ProductID,
Inserted.Quantity,
Inserted.ModifiedDate,
Deleted.ProductID,
Deleted.Quantity,
Deleted.ModifiedDate;
GO
EXECUTE Production.usp_UpdateInventory '20030501';
CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
SELECT ProductID,
SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate = @OrderDate
GROUP BY ProductID
) AS src(ProductID, OrderQty)
ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
AND tgt.Quantity - src.OrderQty <= 0
THEN
DELETE
WHEN MATCHED
THEN
UPDATE
SET tgt.Quantity = tgt.Quantity - src.OrderQty,
tgt.ModifiedDate = GETDATE();
GO
EXECUTE Production.usp_UpdateInventory '20030501';
C. Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla de destino mediante una tabla de origen derivada
En el ejemplo siguiente se usa MERGE para modificar la tabla SalesReason
de la base de datos AdventureWorks2022, mediante la actualización o inserción de filas.
Cuando el valor de NewName
de la tabla de origen coincide con un valor de la columna Name
de la tabla de destino, (SalesReason
), la columna ReasonType
se actualiza en la tabla de destino. Cuando el valor de NewName
no coincide, la fila de origen se inserta en la tabla de destino. La tabla de origen es una tabla derivada que usa la característica de constructor con valores de tabla de Transact-SQL para especificar varias filas en la tabla de origen. Para obtener más información sobre cómo usar el constructor con valores de tabla en una tabla derivada, vea Constructor con valores de tabla (Transact-SQL).
La cláusula OUTPUT puede ser útil para consultar el resultado de las instrucciones MERGE; para obtener más información, vea Cláusula OUTPUT. El ejemplo también muestra cómo almacenar los resultados de la cláusula OUTPUT en una variable de tabla. Y, después, se resumen los resultados de la instrucción MERGE mediante la ejecución de una simple operación de selección que devuelve el número de filas insertadas y actualizadas.
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));
MERGE INTO Sales.SalesReason AS tgt
USING (
VALUES ('Recommendation', 'Other'),
('Review', 'Marketing'),
('Internet', 'Promotion')
) AS src(NewName, NewReasonType)
ON tgt.Name = src.NewName
WHEN MATCHED
THEN
UPDATE
SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
THEN
INSERT (Name, ReasonType)
VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;
-- Query the results of the table variable.
SELECT Change,
COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;
Cuando el valor de NewName
de la tabla de origen coincide con un valor de la columna Name
de la tabla de destino, (SalesReason
), la columna ReasonType
se actualiza en la tabla de destino. Cuando el valor de NewName
no coincide, la fila de origen se inserta en la tabla de destino. La tabla de origen es una tabla derivada que usa SELECT ... UNION ALL
para especificar varias filas en la tabla de origen.
MERGE INTO Sales.SalesReason AS tgt
USING (
SELECT 'Recommendation', 'Other'
UNION ALL
SELECT 'Review', 'Marketing'
UNION ALL
SELECT 'Internet', 'Promotion'
) AS src(NewName, NewReasonType)
ON tgt.Name = src.NewName
WHEN MATCHED
THEN
UPDATE SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
THEN
INSERT (Name, ReasonType)
VALUES (NewName, NewReasonType);
D. Insertar los resultados de la instrucción MERGE en otra tabla
En el ejemplo siguiente se capturan los datos devueltos por la cláusula OUTPUT de una instrucción MERGE y se insertan en otra tabla. La instrucción MERGE actualiza diariamente la columna Quantity
de la tabla ProductInventory
de la base de datos AdventureWorks2022, en función de los pedidos procesados en la tabla SalesOrderDetail
. En el ejemplo se capturan las filas actualizadas y se insertan en otra tabla que se usa para realizar el seguimiento de los cambios del inventario.
CREATE TABLE Production.UpdatedInventory (
ProductID INT NOT NULL,
LocationID INT,
NewQty INT,
PreviousQty INT,
CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
ProductID,
LocationID
)
);
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
MERGE Production.ProductInventory AS pi
USING (
SELECT ProductID, SUM(OrderQty)
FROM Sales.SalesOrderDetail AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
AND soh.OrderDate BETWEEN '20030701'
AND '20030731'
GROUP BY ProductID
) AS src(ProductID, OrderQty)
ON pi.ProductID = src.ProductID
WHEN MATCHED
AND pi.Quantity - src.OrderQty >= 0
THEN
UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED
AND pi.Quantity - src.OrderQty <= 0
THEN
DELETE
OUTPUT $action,
Inserted.ProductID,
Inserted.LocationID,
Inserted.Quantity AS NewQty,
Deleted.Quantity AS PreviousQty
) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO
E. Usar MERGE para realizar una operación INSERT o UPDATE en una tabla perimetral de destino en una base de datos de gráficos
En este ejemplo, se crean las tablas de nodo Person
y City
, así como una tabla perimetral livesIn
. Utilice la instrucción MERGE en la tabla perimetral livesIn
para insertar una fila nueva si aún no existe el perímetro entre Person
y City
. Si ya existe el perímetro, simplemente se actualiza el atributo StreetAddress en la tabla perimetral livesIn
.
-- CREATE node and edge tables
CREATE TABLE Person
(
ID INTEGER PRIMARY KEY,
PersonName VARCHAR(100)
)
AS NODE
GO
CREATE TABLE City
(
ID INTEGER PRIMARY KEY,
CityName VARCHAR(100),
StateName VARCHAR(100)
)
AS NODE
GO
CREATE TABLE livesIn
(
StreetAddress VARCHAR(100)
)
AS EDGE
GO
-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO
INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO
INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO
-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
@PersonId integer,
@CityId integer,
@StreetAddress varchar(100)
AS
BEGIN
MERGE livesIn
USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
JOIN Person ON T.PersonId = Person.ID
JOIN City ON T.CityId = City.ID)
ON MATCH (Person-(livesIn)->City)
WHEN MATCHED THEN
UPDATE SET StreetAddress = @StreetAddress
WHEN NOT MATCHED THEN
INSERT ($from_id, $to_id, StreetAddress)
VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO
-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO
-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO
-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO