MERGE (Transact-SQL)MERGE (Transact-SQL)

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

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.Runs insert, update, or delete operations on a target table from the results of a join with a source table. 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.For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Sugerencia de rendimiento: el comportamiento condicional descrito para la instrucción MERGE funciona mejor cuando las dos tablas tienen una mezcla compleja de características coincidentes.Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. Por ejemplo, insertar una fila si no existe o actualizar una fila si coincide.For example, inserting a row if it doesn't exist, or updating a row if it matches. Cuando simplemente se actualiza una tabla basada en las filas de otra tabla, mejore el rendimiento y la escalabilidad con las instrucciones básicas INSERT, UPDATE y DELETE.When simply updating one table based on the rows of another table, improve the performance and scalability with basic INSERT, UPDATE, and DELETE statements. Por ejemplo:For example:

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

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

[ WITH <common_table_expression> [,...n] ]  
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]  
    USING <table_source>
    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 . ]  
  target_table  
}  
  
<merge_hint>::=  
{  
    { [ <table_hint_limited> [ ,...n ] ]  
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }  
}  
  
<table_source> ::=
{  
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]
        [ WITH ( table_hint [ [ , ]...n ] ) ]
  | rowset_function [ [ AS ] table_alias ]
        [ ( bulk_column_alias [ ,...n ] ) ]
  | user_defined_function [ [ AS ] table_alias ]  
  | OPENXML <openxml_clause>
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
  | <joined_table>
  | <pivoted_table>
  | <unpivoted_table>
}  
  
<merge_search_condition> ::=  
    <search_condition>  
  
<merge_matched>::=  
    { UPDATE SET <set_clause> | DELETE }  
  
<set_clause>::=  
SET  
  { column_name = { expression | DEFAULT | NULL }  
  | { udt_column_name.{ { property_name = expression  
                        | field_name = expression }  
                        | method_name ( argument [ ,...n ] ) }  
    }  
  | column_name { .WRITE ( expression , @Offset , @Length ) }  
  | @variable = expression  
  | @variable = column = expression  
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  
  } [ ,...n ]
  
<merge_not_matched>::=  
{  
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )  
        | DEFAULT VALUES }  
}  
  
<clause_search_condition> ::=  
    <search_condition>  
  
<search condition> ::=  
    MATCH(<graph_search_pattern>) | <search_condition_without_match> | <search_condition> AND <search_condition>

<search_condition_without_match> ::=
    { [ NOT ] <predicate> | ( <search_condition_without_match> )
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition_without_match> ) } ]
[ ,...n ]  

<predicate> ::=
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression
    | string_expression [ NOT ] LIKE string_expression
  [ ESCAPE 'escape_character' ]
    | expression [ NOT ] BETWEEN expression AND expression
    | expression IS [ NOT ] NULL
    | CONTAINS
  ( { column | * } , '< contains_search_condition >' )
    | FREETEXT ( { column | * } , 'freetext_string' )
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] )
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
  { ALL | SOME | ANY} ( subquery )
    | EXISTS ( subquery ) }

<graph_search_pattern> ::=
    { <node_alias> {
                      { <-( <edge_alias> )- }
                    | { -( <edge_alias> )-> }
                    <node_alias>
                   }
    }
  
<node_alias> ::=
    node_table_name | node_table_alias

<edge_alias> ::=
    edge_table_name | edge_table_alias

<output_clause>::=  
{  
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }  
        [ (column_list) ] ]  
    [ OUTPUT <dml_select_list> ]  
}  
  
<dml_select_list>::=  
    { <column_name> | scalar_expression }
        [ [AS] column_alias_identifier ] [ ,...n ]  
  
<column_name> ::=  
    { DELETED | INSERTED | from_table_name } . { * | column_name }  
    | $action  

ArgumentosArguments

WITH <common_table_expression>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.Specifies the temporary named result set or view, also known as common table expression, that's defined within the scope of the MERGE statement. El conjunto de resultados deriva de una consulta simple. La instrucción MERGE hace referencia al conjunto de resultados.The result set derives from a simple query and is referenced by the MERGE statement. Para más información, vea WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression ) [ PERCENT ]
Especifica el número o porcentaje de filas afectadas.Specifies the number or percentage of affected rows. expression puede ser un número o un porcentaje de las filas.expression can be either a number or a percentage of the rows. Las filas a las que se hace referencia en la expresión TOP no están organizadas en ningún orden.The rows referenced in the TOP expression are not arranged in any order. Para más información, vea TOP (Transact-SQL).For more information, see 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.The TOP clause applies after the entire source table and the entire target table join and the joined rows that don't qualify for an insert, update, or delete action are removed. La cláusula TOP reduce aún más el número de filas unidas al valor especificado.The TOP clause further reduces the number of joined rows to the specified value. Las acciones de inserción, actualización o eliminación se aplican a las filas unidas restantes de forma desordenada.The insert, update, or delete actions apply to the remaining joined rows in an unordered way. Es decir, no hay ningún orden en el que las filas se distribuyan entre las acciones definidas en las cláusulas WHEN.That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. Por ejemplo, si se especifica TOP (10), afectará a 10 filas.For example, specifying TOP (10) affects 10 rows. De estas filas, 7 se pueden actualizar y 3 insertar, o se puede eliminar 1, actualizar 5 e insertar 4, y así sucesivamente.Of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on.

Dado que la instrucción MERGE realiza recorridos de tabla completos de ambas tablas, de destino y de origen, el rendimiento de E/S a veces se ve afectado al utilizar la cláusula TOP para modificar una tabla grande mediante la creación de varios lotes.Because the MERGE statement does a full table scan of both the source and target tables, I/O performance is sometimes affected when using the TOP clause to modify a large table by creating multiple batches. En este escenario, es importante asegurarse de que todos los lotes sucesivos tengan como destino nuevas filas.In this scenario, it's important to ensure that all successive batches target new rows.

database_namedatabase_name
El nombre de la base de datos donde se encuentra target_table.The name of the database in which target_table is located.

schema_nameschema_name
El nombre del esquema al que pertenece target_table.The name of the schema to which target_table belongs.

target_tabletarget_table
La tabla o la vista con la que se hacen coincidir las filas de datos de <table_source> según <clause_search_condition>.The table or view against which the data rows from <table_source> are matched based on <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.target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

Si target_table es una vista, cualquier acción con ella debe satisfacer las condiciones para actualizar las vistas.If target_table is a view, any actions against it must satisfy the conditions for updating views. Para más información, vea Modificar datos mediante una vista.For more information, see Modify Data Through a View.

target_table no puede ser una tabla remota.target_table can't be a remote table. target_table no puede tener ninguna regla definida.target_table can't have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
Un nombre alternativo para hacer referencia a una tabla.An alternative name to reference a table.

USING <table_source>USING <table_source>
Especifica el origen de datos que se hace coincidir con las filas de datos de target_table según <merge_search condition>.Specifies the data source that's matched with the data rows in target_table based on <merge_search condition>. El resultado de esta coincidencia dicta las acciones que tomarán las cláusulas WHEN de la instrucción MERGE.The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. <table_source> puede ser una tabla remota o una tabla derivada que tenga acceso a las tablas remotas.<table_source> can be a remote table or a derived table that accesses remote tables.

<table_source> puede ser una tabla derivada que use el constructor con valores de tabla de Transact-SQLTransact-SQL para construir una tabla especificando varias filas.<table_source> can be a derived table that uses the Transact-SQLTransact-SQL table value constructor to construct a table by specifying multiple rows.

Para más información sobre la sintaxis y los argumentos de esta cláusula, vea FROM (Transact-SQL).For more information about the syntax and arguments of this clause, see FROM (Transact-SQL).

ON <merge_search_condition>ON <merge_search_condition>
Especifica las condiciones en las que table_source> se combina con <target_table para determinar dónde coinciden.Specifies the conditions on which <table_source> joins with target_table to determine where they match.

Precaución

Es importante especificar solamente las columnas de la tabla de destino que se utilizan para los propósitos de la coincidencia.It's important to specify only the columns from the target table to use for matching purposes. Es decir, especifique las columnas de la tabla de destino que se comparan con la correspondiente columna de la tabla de origen.That is, specify columns from the target table that are compared to the corresponding column of the source table. 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.Don't attempt to improve query performance by filtering out rows in the target table in the ON clause; for example, such as specifying AND NOT target_table.column_x = value. Si se hace esto, se pueden devolver resultados inesperados e incorrectos.Doing so may return unexpected and incorrect results.

WHEN MATCHED THEN <merge_matched>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>.Specifies that all rows of *target_table, which match the rows returned by <table_source> ON <merge_search_condition>, and satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause.

La instrucción MERGE puede tener, a lo sumo, dos cláusulas WHEN MATCHED.The MERGE statement can have, at most, two WHEN MATCHED clauses. Si se especifican dos cláusulas, la primera debe ir acompañada de una cláusula AND <search_condition>.If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. Para una fila determinada, la segunda cláusula WHEN MATCHED se aplica solamente si no se aplica la primera.For any given row, the second WHEN MATCHED clause is only applied if the first isn't. Si hay dos cláusulas WHEN MATCHED, una debe especificar una acción UPDATE y la otra una acción DELETE.If there are two WHEN MATCHED clauses, one must specify an UPDATE action and one must specify a DELETE action. Si se especifica UPDATE en la cláusula <merge_matched> y más de una fila de <table_source> coincide con una fila de target_table según <merge_search_condition>, SQL ServerSQL Server devuelve un error.When UPDATE is specified in the <merge_matched> clause, and more than one row of <table_source> matches a row in target_table based on <merge_search_condition>, SQL ServerSQL Server returns an error. La instrucción MERGE no puede actualizar la misma fila más de una vez, ni actualizar o eliminar la misma fila.The MERGE statement can't update the same row more than once, or update and delete the same row.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>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.Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that doesn't match a row in target_table, but satisfies an additional search condition, if present. La cláusula <merge_not_matched> especifica los valores que se van a insertar.The values to insert are specified by the <merge_not_matched> clause. La instrucción MERGE puede tener solamente una cláusula WHEN NOT MATCHED.The MERGE statement can have only one WHEN NOT MATCHED clause.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>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>.Specifies that all rows of *target_table, which don't match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition, are updated or deleted according to the <merge_matched> clause.

La instrucción MERGE puede tener a lo sumo dos cláusulas WHEN NOT MATCHED BY SOURCE.The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. Si se especifican dos cláusulas, la primera debe ir acompañada de una cláusula AND <clause_search_condition>.If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. Para una fila determinada, la segunda cláusula WHEN NOT MATCHED BY SOURCE se aplica solamente si no se aplica la primera.For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first isn't. Si hay dos cláusulas WHEN NOT MATCHED BY SOURCE, una debe especificar una acción UPDATE y la otra una acción DELETE.If there are two WHEN NOT MATCHED BY SOURCE clauses, then one must specify an UPDATE action and one must specify a DELETE action. Solamente se puede hacer referencia a las columnas de la tabla de destino en <clause_search_condition>.Only columns from the target table can be referenced in <clause_search_condition>.

Cuando <table_source> no devuelve ninguna fila, no se puede tener acceso a las columnas de la tabla de origen.When no rows are returned by <table_source>, columns in the source table can't be accessed. 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).If the update or delete action specified in the <merge_matched> clause references columns in the source table, error 207 (Invalid column name) is returned. 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.For example, the clause WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 may cause the statement to fail because Col1 in the source table is inaccessible.

AND <clause_search_condition>AND <clause_search_condition>
Especifica cualquier condición de búsqueda válida.Specifies any valid search condition. Para más información, vea Condición de búsqueda (Transact-SQL).For more information, see Search Condition (Transact-SQL).

<table_hint_limited><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.Specifies one or more table hints to apply on the target table for each of the insert, update, or delete actions done by the MERGE statement. La palabra clave WITH y los paréntesis son obligatorios.The WITH keyword and the parentheses are required.

No se permiten NOLOCK ni READUNCOMMITTED.NOLOCK and READUNCOMMITTED aren't allowed. Para más información sobre las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).For more information about table hints, see Table Hints (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.Specifying the TABLOCK hint on a table that's the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. Se realiza un bloqueo exclusivo en la tabla.An exclusive lock is taken on the table. Cuando se especifica FORCESEEK, se aplica a la instancia implícita de la tabla de destino combinada con la tabla de origen.When FORCESEEK is specified, it applies to the implicit instance of the target table joined with the source table.

Precaución

Si se especifica READPAST con WHEN NOT MATCHED [ BY TARGET ] THEN INSERT, pueden producirse operaciones INSERT que infrinjan las restricciones UNIQUE.Specifying READPAST with WHEN NOT MATCHED [ BY TARGET ] THEN INSERT may result in INSERT operations that violate UNIQUE constraints.

INDEX ( index_val [ ,...n ] )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.Specifies the name or ID of one or more indexes on the target table for doing an implicit join with the source table. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).For more information, see Table Hints (Transact-SQL).

<output_clause><output_clause>
Devuelve una fila para cada fila de target_table que se actualiza, inserta o elimina, sin seguir ningún orden concreto.Returns a row for every row in target_table that's updated, inserted, or deleted, in no particular order. $action se puede especificar en la cláusula de salida.$action can be specified in the output clause. $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.$action is a column of type nvarchar(10) that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action done on that row. Para más información sobre la sintaxis y los argumentos de esta cláusula, vea Cláusula OUTPUT (Transact-SQL).For more information about the arguments of this clause, see OUTPUT Clause (Transact-SQL).

OPTION ( <query_hint> [ ,...n ] )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.Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).For more information, see Query Hints (Transact-SQL).

<merge_matched><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.Specifies the update or delete action that's applied to all rows of target_table that don't match the rows returned by <table_source> ON <merge_search_condition>, and which satisfy any additional search condition.

UPDATE SET <set_clause>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.Specifies the list of column or variable names to update in the target table and the values with which to update them.

Para más información sobre la sintaxis y los argumentos de esta cláusula, vea UPDATE (Transact-SQL).For more information about the arguments of this clause, see UPDATE (Transact-SQL). No se admite el establecimiento de una variable con el mismo valor que una columna.Setting a variable to the same value as a column isn't supported.

DeleteDELETE
Especifica que se eliminarán las filas que coincidan con las filas de target_table.Specifies that the rows matching rows in target_table are deleted.

<merge_not_matched><merge_not_matched>
Especifica los valores que insertar en la tabla de destino.Specifies the values to insert into the target table.

(column_list)(column_list)
Una lista de una o varias columnas de la tabla de destino en la que insertar los datos.A list of one or more columns of the target table in which to insert data. Las columnas se deben especificar como un nombre de una sola parte o, de lo contrario, se producirá un error en la instrucción MERGE.Columns must be specified as a single-part name or else the MERGE statement will fail. column_list debe ir entre paréntesis y delimitada con comas.column_list must be enclosed in parentheses and delimited by commas.

VALUES ( values_list)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.A comma-separated list of constants, variables, or expressions that return values to insert into the target table. Las expresiones no pueden contener una instrucción EXECUTE.Expressions can't contain an EXECUTE statement.

DEFAULT VALUESDEFAULT VALUES
Hace que la fila insertada contenga los valores predeterminados definidos para cada columna.Forces the inserted row to contain the default values defined for each column.

Para más información sobre esta cláusula, vea INSERT (Transact-SQL).For more information about this clause, see INSERT (Transact-SQL).

<search condition><search condition>
Indica las condiciones de búsqueda para especificar <merge_search_condition> o <clause_search_condition>.Specifies the search conditions to specify <merge_search_condition> or <clause_search_condition>. Para más información sobre los argumentos de esta cláusula, vea Condiciones de búsqueda (Transact-SQL).For more information about the arguments for this clause, see Search Condition (Transact-SQL).

<graph search pattern><graph search pattern>
Especifica el patrón de coincidencia de gráficos.Specifies the graph match pattern. Para obtener más información sobre los argumentos de esta cláusula, vea MATCH (Transact-SQL)For more information about the arguments for this clause, see MATCH (Transact-SQL)

NotasRemarks

Al menos se debe especificar una de las tres cláusulas MATCHED, pero se pueden especificar en cualquier orden.At least one of the three MATCHED clauses must be specified, but they can be specified in any order. Una variable no puede actualizarse más de una vez en la misma cláusula MATCHED.A variable can't be updated more than once in the same MATCHED clause.

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.Any insert, update, or delete action specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. Si IGNORE_DUP_KEY es ON para algún índice único de la tabla de destino, MERGE omite este valor.If IGNORE_DUP_KEY is ON for any unique indexes on the target table, MERGE ignores this setting.

La instrucción MERGE requiere un punto y coma (;) como terminador.The MERGE statement requires a semicolon (;) as a statement terminator. Se genera el error 10713 cuando una instrucción MERGE se ejecuta sin el terminador.Error 10713 is raised when a MERGE statement is run without the terminator.

Cuando se usa después de MERGE, @@ROWCOUNT (Transact-SQL) devuelve el número total de filas insertadas, actualizadas y eliminadas al cliente.When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

MERGE es una palabra clave totalmente reservada cuando el nivel de compatibilidad de la base de datos se establece en 100 o superior.MERGE is a fully reserved keyword when the database compatibility level is set to 100 or higher. 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 se establece en 90.The MERGE statement is available under both 90 and 100 database compatibility levels; however, the keyword isn't fully reserved when the database compatibility level is set to 90.

No use la instrucción MERGE cuando se usa la replicación de actualización en cola.Don't use the MERGE statement when using queued updating replication. MERGE y el desencadenador de actualización en cola no son compatibles.The MERGE and queued updating trigger aren't compatible. Reemplace la instrucción MERGE con una instrucción de inserción o de actualización.Replace the MERGE statement with an insert or an update statement.

Implementación de desencadenadoresTrigger Implementation

Para cada acción de inserción, actualización o eliminación especificada en la instrucción MERGE, SQL ServerSQL Server activa los desencadenadores AFTER correspondientes definidos en la tabla de destino, pero no garantiza qué acción activará los desencadenadores primero o último.For every insert, update, or delete action specified in the MERGE statement, SQL ServerSQL Server fires any corresponding AFTER triggers defined on the target table, but doesn't guarantee on which action to fire triggers first or last. Los desencadenadores definidos para la misma acción cumplen el orden que especifique.Triggers defined for the same action honor the order you specify. 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.For more information about setting trigger firing order, see Specify First and Last Triggers.

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.If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action done by a MERGE statement, it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

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.If any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers are defined on target_table, the update or delete operations aren't run. En su lugar, se activan los desencadenadores y las tablas inserted y deleted se rellenan en consecuencia.Instead, the triggers fire and the inserted and deleted tables then populate accordingly.

Si se definen desencadenadores INSTEAD OF INSERT en target_table, la operación de inserción no se realiza.If any INSTEAD OF INSERT triggers are defined on target_table, the insert operation isn't performed. En su lugar, la tabla se rellena en consecuencia.Instead, the table populates accordingly.

PermisosPermissions

Requiere el permiso SELECT en la tabla de origen y los permisos INSERT, UPDATE o DELETE en la tabla de destino.Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. Para más información, consulte la sección Permisos de los artículos SELECT, INSERT, UPDATE y DELETE.For more information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE articles.

Optimizar el rendimiento de la instrucción MERGEOptimizing MERGE Statement Performance

Mediante la instrucción MERGE, puede reemplazar las instrucciones DML individuales con una instrucción única.By using the MERGE statement, you can replace the individual DML statements with a single statement. 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.This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed. Pero las mejoras en el rendimiento dependerán de si hay índices, combinaciones y otras consideraciones correctas en su lugar.However, performance gains depend on having correct indexes, joins, and other considerations in place.

Prácticas recomendadas para índicesIndex Best Practices

Para mejorar el rendimiento de la instrucción MERGE, recomendamos las siguientes instrucciones de índices:To improve the performance of the MERGE statement, we recommend the following index guidelines:

  • Cree un índice en las columnas de combinación de la tabla de origen que sea única y de cobertura.Create an index on the join columns in the source table that is unique and covering.
  • Cree un índice en un clúster único en las columnas de combinación de la tabla de destino.Create a unique clustered index on the join columns in the target table.

Estos índices aseguran que las claves de combinación son únicas y los datos de las tablas están ordenados.These indexes ensure that the join keys are unique and the data in the tables is sorted. 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, y no son necesarias operaciones de ordenación adicionales.Query performance is improved because the query optimizer does not need to perform extra validation processing to locate and update duplicate rows and additional sort operations are not necessary.

Prácticas recomendadas para JOINJOIN Best Practices

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:To improve the performance of the MERGE statement and ensure correct results are obtained, we recommend the following join guidelines:

  • 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.Specify only search conditions in the ON <merge_search_condition> clause that determine the criteria for matching data in the source and target tables. Es decir, especifique solo las columnas de la tabla de destino que se comparan con las correspondientes columnas de la tabla de origen.That is, specify only columns from the target table that are compared to the corresponding columns of the source table.
  • No incluya comparaciones a otros valores como una constante.Do not include comparisons to other values such as a constant.

Para filtrar las filas de las tablas de origen o de destino, use uno de los métodos siguientes.To filter out rows from the source or target tables, use one of the following methods.

  • Especifique la condición de búsqueda para el filtrado de filas en la cláusula WHEN adecuada.Specify the search condition for row filtering in the appropriate WHEN clause. Por ejemplo, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...For example, 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.Define a view on the source or target that returns the filtered rows and reference the view as the source or target table. Si se define la vista en la tabla de destino, cualquier acción con ella debe satisfacer las condiciones para actualizar las vistas.If the view is defined on the target table, any actions against it must satisfy the conditions for updating views. Para obtener más información acerca de cómo actualizar datos mediante una vista, consulte Modificar datos mediante una vista.For more information about updating data by using a view, see Modifying Data Through a View.
  • Use la cláusula WITH <common table expression> para filtrar filas de las tablas de origen o de destino.Use the WITH <common table expression> clause to filter out rows from the source or target tables. Este método es similar a especificar el criterio de búsqueda adicional en la cláusula ON y puede generar resultados incorrectos.This method is similar to specifying additional search criteria in the ON clause and may produce incorrect results. Se recomienda evitar el uso de este método o prueba de manera exhaustiva antes de implementarlo.We recommend that you avoid using this method or test thoroughly before implementing it.

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.The join operation in the MERGE statement is optimized in the same way as a join in a SELECT statement. 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.That is, when SQL Server processes joins, the query optimizer chooses the most efficient method (out of several possibilities) of processing the join. 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.When the source and target are of similar size and the index guidelines described previously are applied to the source and target tables, a merge join operator is the most efficient query plan. Esto es debido a que ambas tablas se examinan una vez y no hay necesidad de ordenar los datos.This is because both tables are scanned once and there is no need to sort the data. Cuando el origen es menor que la tabla de destino, es preferible usar un operador de bucles anidados.When the source is smaller than the target table, a nested loops operator is preferable.

Puede exigir el uso de una combinación concreta especificando la cláusula OPTION (<query_hint>) en la instrucción MERGE.You can force the use of a specific join by specifying the OPTION (<query_hint>) clause in the MERGE statement. 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.We recommend that you do not use the hash join as a query hint for MERGE statements because this join type does not use indexes.

Prácticas recomendadas para parametrizaciónParameterization Best Practices

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.If a SELECT, INSERT, UPDATE, or DELETE statement is executed without parameters, the SQL Server query optimizer may choose to parameterize the statement internally. Esto indica que todos los valores literales incluidos en la consulta se sustituirán por parámetros.This means that any literal values that are contained in the query are substituted with parameters. 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).For example, the statement INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), may be implemented internally as 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.This process, called simple parameterization, increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans. Se puede mejorar el rendimiento de las consultas debido a que se reduce la frecuencia de las compilaciones y recompilaciones de la consulta.Query performance may be improved because the frequency of query compilations and recompilations are reduced. El optimizador de consultas no aplica el proceso de parametrización simple a las instrucciones MERGE.The query optimizer does not apply the simple parameterization process to MERGE statements. Por consiguiente, puede que no se realicen las instrucciones MERGE que contienen los 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.Therefore, MERGE statements that contain literal values may not perform as well as individual INSERT, UPDATE, or DELETE statements because a new plan is compiled each time the MERGE statement is executed.

Para mejorar el rendimiento de las consultas, recomendamos las siguientes instrucciones de parametrización:To improve query performance, we recommend the following parameterization guidelines:

  • Parametrice todos los valores literales en la cláusula ON <merge_search_condition> y en las cláusulas WHEN de la instrucción MERGE.Parameterize all literal values in the ON <merge_search_condition> clause and in the the WHEN clauses of the MERGE statement. Por ejemplo, puede incorporar la instrucción MERGE en un procedimiento almacenado que reemplaza los valores literales con parámetros de entrada adecuados.For example, you can incorporate the MERGE statement into a stored procedure replacing the literal values with appropriate input parameters.
  • 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.If you cannot parameterize the statement, create a plan guide of type TEMPLATE and specify the PARAMETERIZATION FORCED query hint in the plan guide.
  • 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.If MERGE statements are executed frequently on the database, consider setting the PARAMETERIZATION option on the database to FORCED. Actúe con precaución cuando establezca esta opción.Use caution when setting this option. 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.The PARAMETERIZATION option is a database-level setting and affects how all queries against the database are processed.

Prácticas recomendadas para la cláusula TOPTOP Clause Best Practices

En la instrucción MERGE, la cláusula TOP especifica el número o porcentaje de filas afectadas después de que la tabla de origen y la tabla de destino se combinen, y después de quitar las filas que no cumplen los requisitos para una acción de inserción, actualización o eliminación.In the MERGE statement, the TOP clause specifies the number or percentage of rows that are affected after the source table and the target table are joined, and after rows that do not qualify for an insert, update, or delete action are removed. 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.The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions are applied to the remaining joined rows in an unordered fashion. Es decir, no hay ningún orden en el que las filas se distribuyan entre las acciones definidas en las cláusulas WHEN.That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. 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.For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted and so on.

Es habitual usar la cláusula TOP para realizar operaciones del lenguaje de manipulación de datos (DML) en una tabla grande en lotes.It is common to use the TOP clause to perform data manipulation language (DML) operations on a large table in batches. Cuando se usa la cláusula TOP en la instrucción MERGE con este fin, es importante comprender las implicaciones siguientes.When using the TOP clause in the MERGE statement for this purpose, it is important to understand the following implications.

  • Puede afectar al rendimiento de E/S.I/O performance may be affected.

    La instrucción MERGE realiza exámenes de tabla completos, tanto de las tablas de destino como de origen.The MERGE statement performs a full table scan of both the source and target tables. 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.Dividing the operation into batches reduces the number of write operations performed per batch; however, each batch will perform a full table scan of the source and target tables. La actividad de lectura resultante puede afectar al rendimiento de la consulta.The resulting read activity may affect the performance of the query.

  • Se pueden producir resultados incorrectos.Incorrect results can occur.

    Es importante asegurarse de que todos los lotes sucesivos se destinen a filas nuevas o puede producirse un comportamiento no deseado como la inserción incorrecta de filas duplicadas en la tabla de destino.It is important to ensure that all successive batches target new rows or undesired behavior such as incorrectly inserting duplicate rows into the target table can occur. 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.This can happen when the source table includes a row that was not in a target batch but was in the overall target table.

  • Para asegurar que los resultados son correctos:To insure correct results:

    • 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 the ON clause to determine which source rows affect existing target rows and which are genuinely new.
    • Use una condición adicional en la cláusula WHEN MATCHED para determinar si un lote anterior ya ha actualizado la fila de destino.Use an additional condition in the WHEN MATCHED clause to determine if the target row has already been updated by a previous batch.

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.Because the TOP clause is only applied after these clauses are applied, each execution either inserts one genuinely unmatched row or updates one existing row.

Prácticas recomendadas para carga masivaBulk Load Best Practices

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.The MERGE statement can be used to efficiently bulk load data from a source data file into a target table by specifying the OPENROWSET(BULK…) clause as the table source. De esta forma, el archivo completo se procesa en un lote único.By doing so, the entire file is processed in a single batch.

Para mejorar el rendimiento del proceso de mezcla masiva, recomendamos las siguientes instrucciones:To improve the performance of the bulk merge process, we recommend the following guidelines:

  • Cree un índice clúster en las columnas de combinación de la tabla de destino.Create a clustered index on the join columns in the target table.

  • Use las sugerencias ORDER y UNIQUE en la cláusula OPENROWSET(BULK…) para especificar cómo se ordena el archivo de datos de origen.Use the ORDER and UNIQUE hints in the OPENROWSET(BULK…) clause to specify how the source data file is sorted.

    De forma predeterminada, la operación masiva presupone que los datos del archivo no están ordenados.By default, the bulk operation assumes the data file is unordered. Por consiguiente, es importante que los datos del origen estén ordenados según el índice clúster 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.Therefore, it is important that the source data is sorted according to the clustered index on the target table and that the ORDER hint is used to indicate the order so that the query optimizer can generate a more efficient query plan. Las sugerencias se validan en tiempo de ejecución; si el flujo de datos no se ajusta a las sugerencias especificadas, se produce un error.Hints are validated at runtime; if the data stream does not conform to the specified hints, an error is raised.

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.These guidelines ensure that the join keys are unique and the sort order of the data in the source file matches the target table. 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.Query performance is improved because additional sort operations are not necessary and unnecessary data copies are not required.

Medir y diagnosticar el rendimiento de MERGEMeasuring and Diagnosing MERGE Performance

Las características siguientes están disponibles para ayudarlo a medir y diagnosticar el rendimiento de las instrucciones MERGE.The following features are available to assist you in measuring and diagnosing the performance of MERGE statements.

  • 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 the merge stmt counter in the sys.dm_exec_query_optimizer_info dynamic management view to return the number of query optimizations that are for MERGE statements.
  • 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 the merge_action_type attribute in the sys.dm_exec_plan_attributes dynamic management view to return the type of trigger execution plan used as the result of a MERGE statement.
  • Use Seguimiento de SQL 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).Use SQL Trace to gather troubleshooting data for the MERGE statement in the same way you would for other data manipulation language (DML) statements. Para más información, consulte SQL Trace.For more information, see SQL Trace.

EjemplosExamples

A.A. Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla en una sola instrucciónUsing MERGE to do INSERT and UPDATE operations on a table in a single statement

Un escenario común es la actualización de una o más columnas de una tabla si existe una línea coincidente.A common scenario is updating one or more columns in a table if a matching row exists. O bien, con la inserción de los datos como una nueva fila si no existe una fila coincidente.Or, inserting the data as a new row if a matching row doesn't exist. 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.You usually do either scenario by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. Con la instrucción MERGE puede realizar ambas tareas en una sola instrucción.With the MERGE statement, you can do both tasks in a single statement. En el ejemplo siguiente se muestra un procedimiento almacenado de la base de datos AdventureWorks2012AdventureWorks2012 que contiene una instrucción INSERT y una instrucción UPDATE.The following example shows a stored procedure in the AdventureWorks2012AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. A continuación, el procedimiento se modifica para ejecutar las operaciones equivalentes utilizando una sola instrucción MERGE.The procedure is then modified to run the equivalent operations by using a single MERGE statement.

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 target  
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)  
    ON (target.UnitMeasureCode = source.UnitMeasureCode)  
    WHEN MATCHED THEN
        UPDATE SET Name = source.Name  
    WHEN NOT MATCHED THEN  
        INSERT (UnitMeasureCode, Name)  
        VALUES (source.UnitMeasureCode, source.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.B. Usar MERGE para realizar operaciones UPDATE y DELETE en una tabla en una sola instrucciónUsing MERGE to do UPDATE and DELETE operations on a table in a single statement

El siguiente ejemplo usa MERGE para actualizar a diario la tabla ProductInventory de la base de datos de muestra AdventureWorks2012AdventureWorks2012, en función de los pedidos procesados en la tabla SalesOrderDetail.The following example uses MERGE to update the ProductInventory table in the AdventureWorks2012AdventureWorks2012 sample database, daily, based on orders that are processed in the SalesOrderDetail table. 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.The Quantity column of the ProductInventory table is updated by subtracting the number of orders placed each day for each product in the SalesOrderDetail table. 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.If the number of orders for a product drops the inventory level of a product to 0 or less, the row for that product is deleted from the ProductInventory table.

CREATE PROCEDURE Production.usp_UpdateInventory  
    @OrderDate datetime  
AS  
MERGE Production.ProductInventory AS target  
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod  
    JOIN Sales.SalesOrderHeader AS soh  
    ON sod.SalesOrderID = soh.SalesOrderID  
    AND soh.OrderDate = @OrderDate  
    GROUP BY ProductID) AS source (ProductID, OrderQty)  
ON (target.ProductID = source.ProductID)  
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0  
    THEN DELETE  
WHEN MATCHED
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,
                    target.ModifiedDate = GETDATE()  
OUTPUT $action, Inserted.ProductID, Inserted.Quantity,
    Inserted.ModifiedDate, Deleted.ProductID,  
    Deleted.Quantity, Deleted.ModifiedDate;  
GO  
  
EXECUTE Production.usp_UpdateInventory '20030501'  

C.C. Usar MERGE para realizar operaciones INSERT y UPDATE en una tabla de destino mediante una tabla de origen derivadaUsing MERGE to do UPDATE and INSERT operations on a target table by using a derived source table

En el ejemplo siguiente se usa MERGE para modificar la tabla SalesReason de la base de datos AdventureWorks2012AdventureWorks2012, actualizando o insertando las filas.The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012AdventureWorks2012 database by either updating or inserting rows. 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.When the value of NewName in the source table matches a value in the Name column of the target table, (SalesReason), the ReasonType column is updated in the target table. Cuando el valor de NewName no coincide, la fila de origen se inserta en la tabla de destino.When the value of NewName doesn't match, the source row is inserted into the target table. La tabla de origen es una tabla derivada que usa la característica de constructor con valores de tabla de Transact-SQLTransact-SQL para especificar varias filas en la tabla de origen.The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table. Para saber más sobre cómo usar el constructor con valores de tabla en una tabla derivada, vea Constructor con valores de tabla (Transact-SQL).For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). El ejemplo también muestra cómo almacenar los resultados de la cláusula OUTPUT en una variable de tabla.The example also shows how to store the results of the OUTPUT clause in a table variable. 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.And, then you summarize the results of the MERGE statement by running a simple select operation that returns the count of inserted and updated rows.

-- Create a temporary table variable to hold the output actions.  
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO Sales.SalesReason AS Target  
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'),
              ('Internet', 'Promotion'))  
       AS Source (NewName, NewReasonType)  
ON Target.Name = Source.NewName  
WHEN MATCHED THEN  
UPDATE SET ReasonType = Source.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.D. Insertar los resultados de la instrucción MERGE en otra tablaInserting the results of the MERGE statement into another table

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.The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. La instrucción MERGE actualiza diariamente la columna Quantity de la tabla ProductInventory de la base de datos AdventureWorks2012AdventureWorks2012, en función de los pedidos procesados en la tabla SalesOrderDetail.The MERGE statement updates the Quantity column of the ProductInventory table in the AdventureWorks2012AdventureWorks2012 database, based on orders that are processed in the SalesOrderDetail table. 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.The example captures the updated rows and inserts them into another table that's used to track inventory changes.

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  
            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.E. Usar MERGE para realizar una operación INSERT o UPDATE en una tabla perimetral de destino en una base de datos de gráficosUsing MERGE to do INSERT or UPDATE on a target edge table in a graph database

En este ejemplo, se crean las tablas de nodo Person y City, así como una tabla perimetral livesIn.In this example, you create node tables Person and City and an edge table 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.You use the MERGE statement on the livesIn edge and insert a new row if the edge doesn't already exist between a Person and City. Si ya existe el perímetro, simplemente se actualiza el atributo StreetAddress en la tabla perimetral livesIn.If the edge already exists, then you just update the StreetAddress attribute on the livesIn edge.

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

Consulte tambiénSee Also