MERGE (Transact-SQL)

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance 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.

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>

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>.

[ 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.

[ 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.

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áusulas WHEN 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 consulta PARAMETERIZATION 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

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

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;

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

Contenido relacionado

• SELECT (Transact-SQL)
• INSERT (Transact-SQL)
• UPDATE (Transact-SQL)
• DELETE (Transact-SQL)
• OUTPUT (cláusula de Transact-SQL)
• MERGE en paquetes de Integration Services
• FROM (Transact-SQL)
• Constructor con valores de tabla (Transact-SQL)