FUSIÓN (Transact-SQL)

Aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (solo pool SQL dedicado)Base de datos SQL en Microsoft FabricAlmacén en Microsoft Fabric

La MERGE instrucción ejecuta operaciones de inserción, actualización o eliminación 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.

En este artículo se proporcionan diferentes sintaxis, argumentos, comentarios, permisos y ejemplos basados en la versión del producto seleccionada. Seleccione la versión del producto deseada en la lista desplegable versión.

Convenciones de sintaxis de Transact-SQL

Syntax

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>

Arguments

CON <common_table_expression>

Especifica el conjunto de resultados o la vista con nombre temporal, también conocido como expresión de tabla común, que se define dentro del ámbito de la MERGE instrucción. El conjunto de resultados deriva de una consulta simple y la instrucción hace referencia a este MERGE . Para más información, consulte WITH common_table_expression (Transact-SQL).

TOP ( expresión ) [ 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 TOP expresión no se organizan en ningún orden. Para obtener más información, vea TOP (Transact-SQL).

La TOP cláusula se aplica después de que se quite toda la tabla de origen y toda la combinación de tabla de destino y las filas combinadas que no cumplan los requisitos para una acción de inserción, actualización o eliminación. La TOP cláusula reduce aún más el número de filas combinadas 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 distribuyen entre las acciones definidas en las WHEN cláusulas . Por ejemplo, especificar TOP (10) afecta 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 MERGE instrucción puede realizar un examen de tabla o un examen de índice agrupado en la tabla de origen, así como un examen de tabla o un examen de índice agrupado de la tabla de destino. Por lo tanto, el rendimiento de E/S a veces se ve afectado incluso cuando se usa la TOP cláusula para modificar una tabla grande mediante la creación de varios 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 cualquier operación de inserción, actualización o eliminación especificada por las WHEN cláusulas de la MERGE instrucción .

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

[ COMO ] table_alias

Nombre alternativo para hacer referencia a una tabla para target_table.

USANDO <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 determina las acciones que deben realizar las WHEN cláusulas de la MERGE instrucción . <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.

[ COMO ] 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).

EL <merge_search_condition>

Especifica las condiciones en las que <table_source> se combina con target_table para determinar dónde coinciden.

Caution

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 ON cláusula ; por ejemplo, especificando AND NOT target_table.column_x = value. Si se hace esto, se pueden devolver resultados inesperados e incorrectos.

CUANDO COINCIDA, <MERGE_MATCHED>

Especifica que todas las filas de *target_table, que coinciden con las filas devueltas por <table_source> ON <merge_search_condition>y cumplen cualquier condición de búsqueda adicional, se actualizan o eliminan según la <merge_matched> cláusula .

La MERGE instrucción puede tener, como máximo, dos WHEN MATCHED cláusulas. Si se especifican dos cláusulas, la primera debe ir acompañada de una AND<search_condition> cláusula . Para cualquier fila determinada, la segunda WHEN MATCHED cláusula solo se aplica si la primera no es . Si hay dos WHEN MATCHED cláusulas, una debe especificar una UPDATE acción y otra debe especificar una DELETE acción. Cuando UPDATE se especifica en la <merge_matched> cláusula y más de una fila de <table_source> coincide con una fila de target_table basada en <merge_search_condition>, SQL Server devuelve un error. La MERGE instrucción no puede actualizar la misma fila más de una vez o actualizar y eliminar la misma fila.

CUANDO NO COINCIDE [ 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 en target_table, pero satisface una condición de búsqueda adicional, si existe. La cláusula <merge_not_matched> especifica los valores que se van a insertar. La MERGE instrucción solo puede tener una WHEN NOT MATCHED [ BY TARGET ] cláusula .

CUANDO NO COINCIDA CON EL ORIGEN, <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 cumplen cualquier condición de búsqueda adicional, se actualizan o eliminan según la <merge_matched> cláusula .

La MERGE instrucción puede tener como máximo dos WHEN NOT MATCHED BY SOURCE cláusulas. Si se especifican dos cláusulas, la primera debe ir acompañada de una AND<clause_search_condition> cláusula . Para cualquier fila determinada, la segunda WHEN NOT MATCHED BY SOURCE cláusula solo se aplica si la primera no es . Si hay dos WHEN NOT MATCHED BY SOURCE cláusulas, una debe especificar una UPDATE acción y otra debe especificar una DELETE acción. 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.

Y <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 varias sugerencias de tabla que se aplicarán en la tabla de destino para cada una de las acciones de inserción, actualización o eliminación realizadas por la MERGE instrucción . Se requieren la WITH palabra clave y los paréntesis.

NOLOCK y READUNCOMMITTED no se permiten. Para obtener más información sobre las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).

Especificar la TABLOCK sugerencia en una tabla que es el destino de una INSERT instrucción tiene el mismo efecto que especificar la TABLOCKX sugerencia. 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.

Caution

Especificar READPAST con WHEN NOT MATCHED [ BY TARGET ] THEN INSERT puede dar lugar a INSERT operaciones que infringen UNIQUE restricciones.

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 OUTPUT cláusula es la manera recomendada de consultar o contar 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 de consulta (Transact-SQL).

<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 cumplen cualquier condición de búsqueda adicional.

ACTUALIZACIÓN ESTABLECIDA <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 deben especificarse como un nombre de elemento único o, de lo contrario, se produce un error en la MERGE instrucción . 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 EXECUTE instrucción .

VALORES PREDETERMINADOS

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 Condición de búsqueda (Transact-SQL).

<patrón de búsqueda de grafos>

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

Remarks

El comportamiento condicional descrito para la MERGE instrucción funciona mejor cuando las dos tablas tienen una combinación compleja de características coincidentes. Por ejemplo, insertar una fila si no existe o actualizar una fila si coincide. Al actualizar simplemente una tabla basada en las filas de otra tabla, mejore el rendimiento y la escalabilidad con INSERTlas instrucciones , UPDATEy 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);

Se debe especificar al menos una de las tres MATCHED cláusulas, pero se pueden especificar en cualquier orden. Una variable no se puede actualizar más de una vez en la misma MATCHED cláusula.

Cualquier acción de inserción, actualización o eliminación especificada en la tabla de destino por la MERGE instrucción está limitada por las restricciones definidas en ella, incluidas las restricciones de integridad referencial en cascada. Si IGNORE_DUP_KEY es ON para índices únicos en la tabla de destino, MERGE omite esta configuración.

La MERGE instrucción requiere un punto y coma (;) como terminador de instrucción. Se produce el error 10713 cuando se ejecuta una MERGE instrucción sin el terminador.

Cuando se usa después MERGEde , @@ROWCOUNT (Transact-SQL) devuelve el número total de filas insertadas, actualizadas y eliminadas en el cliente.

MERGE es una palabra clave totalmente reservada cuando el nivel de compatibilidad de la base de datos se establece 100 en o superior. La MERGE instrucción está disponible en los niveles de compatibilidad de 90 bases de datos y 100 ; sin embargo, la palabra clave no está totalmente reservada cuando el nivel de compatibilidad de la base de datos está establecido 90en .

Caution

No use la instrucción al usar la MERGEreplicación de actualización en cola. El MERGE desencadenador de actualización en cola y no son compatibles. Reemplace la MERGE instrucción por una INSERT instrucción y UPDATE .

Implementación del desencadenador

Para cada acción de inserción, actualización o eliminación especificada en la MERGE instrucción , SQL Server desencadena los desencadenadores correspondientes AFTER definidos en la tabla de destino, pero no garantiza en qué acción desencadenar 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 definido un desencadenador OF habilitado INSTEAD para una acción de inserción, actualización o eliminación realizada por una MERGE instrucción , debe tener un desencadenador OF habilitado INSTEAD para todas las acciones especificadas en la MERGE instrucción .

Si se define algún INSTEAD desencadenador OF UPDATE o INSTEAD OF DELETE en target_table, no se ejecutan las operaciones de actualización o eliminación. En su lugar, se activan los desencadenadores y las tablas inserted y deleted se rellenan en consecuencia.

Si se define algún INSTEAD desencadenador OF INSERT en target_table, no se realiza la operación de inserción. En su lugar, la tabla se rellena en consecuencia.

Note

A diferencia de las instrucciones , INSERT, y UPDATE independientesDELETE, el número de filas reflejadas por @@ROWCOUNT dentro de un desencadenador podría ser mayor. Dentro @@ROWCOUNT de cualquier AFTER desencadenador (independientemente de las instrucciones de modificación de datos que captura el desencadenador) reflejará el número total de filas afectadas por .MERGE Por ejemplo, si una MERGE instrucción inserta una fila, actualiza una fila y elimina una fila, @@ROWCOUNT será tres para cualquier AFTER desencadenador, incluso si el desencadenador solo se declara para INSERT las instrucciones .

Permissions

Requiere SELECT permiso en la tabla de origen y INSERTlos permisos , UPDATEo DELETE en la tabla de destino. Para obtener más información, vea la sección Permisos en los artículos SELECT (Transact-SQL), INSERT (Transact-SQL), UPDATE (Transact-SQL) y DELETE (Transact-SQL).

Procedimientos recomendados para índices

Mediante la MERGE instrucción , puede reemplazar las instrucciones DML individuales por una sola instrucción. 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 MERGE instrucción, se recomiendan las siguientes directrices de índice:

• Cree índices para facilitar la combinación entre el origen y el MERGEdestino de :
  • 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 las tablas con cualquier forma de índice de almacén de columnas como destino de MERGE las instrucciones. Al igual que con cualquier UPDATEs, es posible que encuentre un mejor rendimiento con los índices de almacén de columnas actualizando una tabla de almacén de filas almacenada provisionalmente y, a continuación, realizando un procesamiento por DELETE lotes y INSERT, en lugar de o UPDATEMERGE.

Consideraciones de simultaneidad para MERGE

En términos de bloqueo, MERGE es diferente de las instrucciones discretas, consecutivas INSERTUPDATE, y DELETE . MERGEsigue ejecutando las operaciones , INSERTyUPDATE, sin embargo, mediante diferentes mecanismos DELETEde bloqueo. Puede ser más eficaz escribir instrucciones discretas INSERT, UPDATEy DELETE para algunas necesidades de la aplicación. A escala, MERGE puede presentar problemas de simultaneidad complicados o requerir una solución de problemas avanzada. Por lo tanto, planee probar exhaustivamente cualquier MERGE instrucción antes de realizar la implementación en producción.

MERGE Las instrucciones son un reemplazo adecuado para las operaciones discretas INSERT, UPDATEy DELETE en (pero no limitadas a) los escenarios siguientes:

• 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 INSERTlógica independiente , UPDATEy DELETE puede funcionar mejor, con menos bloqueo que una MERGE instrucción .
• 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 las claves únicas se inserte y actualicen mediante MERGE, al especificar se HOLDLOCK evitarán infracciones de clave únicas. HOLDLOCK es un sinónimo del SERIALIZABLE nivel de aislamiento de transacción, 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 conservan bloqueos en intervalos de datos para evitar que las filas fantasma se inserten o actualicen mientras las lecturas están en curso. Para obtener más información sobre HOLDLOCK, vea Sugerencias de tabla y SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Procedimientos recomendados para JOIN

Para mejorar el rendimiento de la MERGE instrucción y asegurarse de que se obtienen resultados correctos, se recomiendan las siguientes directrices de combinación:

• Especifique solo las condiciones de búsqueda en la ON <merge_search_condition> cláusula que determinan los criterios para buscar coincidencias de datos en las tablas de origen y 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 adecuada WHEN . 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 sobre cómo actualizar datos mediante una vista, vea Modificar datos a través de 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 criterios de búsqueda adicionales en la ON cláusula y podría 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 de la MERGE instrucción está optimizada de la misma manera que una combinación en una SELECT instrucción . 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 forzar el uso de una combinación específica especificando la OPTION (<query_hint>) cláusula en la MERGE instrucción . Se recomienda no usar la combinación hash como sugerencia de consulta para MERGE instrucciones porque este tipo de combinación no usa índices.

Procedimientos recomendados para parametrización

Si se ejecuta una SELECTinstrucción , INSERT, UPDATEo DELETE sin parámetros, el optimizador de consultas de SQL Server podría optar por 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 MERGE las instrucciones . Por lo tanto, las MERGE instrucciones que contienen valores literales podrían no realizar e instrucciones individuales INSERT, UPDATEo DELETE porque se compila un nuevo plan cada vez que se ejecuta la MERGE instrucción .

Para mejorar el rendimiento de las consultas, recomendamos las siguientes instrucciones de parametrización:

• Parametrizar todos los valores literales de la ON <merge_search_condition> cláusula y en las WHEN cláusulas de la MERGE instrucción . Por ejemplo, puede incorporar la MERGE instrucción en un procedimiento almacenado reemplazando los valores literales por los 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 MERGE las instrucciones se ejecutan con frecuencia en la base de datos, considere la posibilidad de establecer la PARAMETERIZATION opción en la base de datos FORCEDen . 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 MERGE instrucción , la TOP cláusula especifica el número o porcentaje de filas que se ven afectadas después de que la tabla de origen y la tabla de destino estén unidas, y después de que se quiten las filas que no califican para una acción de inserción, actualización o eliminación. La TOP cláusula reduce aún más el número de filas combinadas al valor especificado y las acciones de inserción, actualización o eliminación se aplican a las filas combinadas restantes de forma no ordenada. Es decir, no hay ningún orden en el que las filas se distribuyen entre las acciones definidas en las WHEN cláusulas . Por ejemplo, especificar TOP (10) afecta a 10 filas; de estas filas, 7 se puede actualizar y 3 insertadas, o 1 podría eliminarse, 5 actualizadas y 4 insertadas, etc.

Es habitual usar la TOP cláusula para realizar operaciones del lenguaje de manipulación de datos (DML) en una tabla grande en lotes. Al usar la TOP cláusula en la MERGE instrucción para este propósito, es importante comprender las siguientes implicaciones.

• Puede afectar al rendimiento de la E/S.

  La MERGE instrucción realiza un examen de tabla completa de las tablas de origen y de destino. 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 ON cláusula para determinar qué filas de origen afectan a las filas de destino existentes y cuáles son realmente nuevas.
  • Use una condición adicional en la WHEN MATCHED cláusula para determinar si la fila de destino ya se actualizó mediante un lote anterior.
  • Use una condición adicional en la WHEN MATCHED cláusula y SET la lógica para comprobar que la misma fila no se puede actualizar dos veces.

Dado que la TOP cláusula solo se aplica después de aplicar estas cláusulas, cada ejecución inserta una fila originalmente no coincide o actualiza una fila existente.

Procedimientos recomendados para la carga masiva

La MERGE instrucción se puede usar para cargar datos de forma eficaz desde un archivo de datos de origen en una tabla de destino especificando la OPENROWSET(BULK...) cláusula como 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 únicos y no agrupados en la tabla de destino durante la carga MERGEmasiva , habilítelos posteriormente. Esto es común y útil para las operaciones de datos masivas nocturnas.

• Use las ORDER sugerencias y UNIQUE de la OPENROWSET(BULK...) cláusula 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 lo tanto, es importante que los datos de origen se ordenen según el índice agrupado de la tabla de destino y que la ORDER sugerencia se use para indicar el orden para que el optimizador de consultas pueda generar un plan de consulta 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 siguientes características están disponibles para ayudarle a medir y diagnosticar el rendimiento de MERGE las instrucciones.

• Use el contador stmt de combinación en la vista de administración dinámica de sys.dm_exec_query_optimizer_info para devolver el número de optimizaciones de consulta que son para MERGE instrucciones.
• Use el merge_action_type atributo de la vista de administración dinámica de sys.dm_exec_plan_attributes para devolver el tipo de plan de ejecución de desencadenador usado como resultado de una MERGE instrucción .
• Use una sesión de eventos extendidos para recopilar datos de solución de problemas para la instrucción de la MERGE misma manera que lo 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.

Examples

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 pasa cualquiera de los parámetros a un procedimiento almacenado que contiene las instrucciones y UPDATE adecuadasINSERT. Con la MERGE instrucción , puede realizar ambas tareas en una sola instrucción. El siguiente ejemplo muestra un procedimiento almacenado en la base de datos AdventureWorks2025 que contiene tanto una INSERT instrucción como una UPDATE sentencia. A continuación, se modifica el procedimiento para ejecutar las operaciones equivalentes mediante una sola MERGE instrucción.

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

El siguiente ejemplo se utiliza MERGE para actualizar la ProductInventory tabla en la base de datos de ejemplo de AdventureWorks2025, diariamente, basándose en los pedidos que se procesan en la SalesOrderDetail tabla. 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

El siguiente ejemplo se utiliza MERGE para modificar la SalesReason tabla en la base de datos AdventureWorks2025 actualizando o insertando 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 OUTPUT cláusula puede ser útil para consultar el resultado de MERGE las instrucciones; para obtener más información, vea cláusula OUTPUT (Transact-SQL). En el ejemplo también se muestra cómo almacenar los resultados de la OUTPUT cláusula en una variable de tabla. Y, a continuación, resumirá los resultados de la MERGE instrucción ejecutando una operación de selección sencilla que devuelva el recuento 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 desde la OUTPUT cláusula de una MERGE instrucción y se insertan esos datos en otra tabla. La MERGE declaración actualiza la Quantity columna de la ProductInventory tabla en la base de datos AdventureWorks2025, basándose en los pedidos que se procesan en la SalesOrderDetail tabla. 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. Use la MERGE instrucción en el livesIn borde e inserte una nueva fila si el borde aún no existe entre y PersonCity. 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)
• INSERTAR (Transact-SQL)
• ACTUALIZACIÓN (Transact-SQL)
• ELIMINAR (Transact-SQL)
• Cláusula OUTPUT (Transact-SQL)
• MERGE en paquetes de Integration Services
• Cláusula FROM más JOIN, APPLY, PIVOT (Transact-SQL)
• Constructor con valores de tabla (Transact-SQL)