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.

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

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

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

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.

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

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

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.

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

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.

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

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

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

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.

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

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.

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

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

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

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