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

本主題適用於:是SQL Server (從 2008 開始)是Azure SQL Database否Azure SQL 資料倉儲 否平行處理資料倉儲 THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

根據與來源資料表聯結的結果,在目標資料表上執行插入、更新或刪除作業。Performs insert, update, or delete operations on a target table based on the results of a join with a source table. 例如,您可以根據在另一個資料表中所找到的差異在資料表中插入、更新或刪除資料列,以同步處理兩個資料表。For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

效能提示: 當兩個資料表有複雜的比對特性時,MERGE 陳述式的條件式行為表現最佳。Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. 例如沒有資料列時插入資料列,或資料列相符時更新資料列。For example, inserting a row if it does not exist, or updating the row if it does match. 只要根據另一個資料表的資料列更新資料表,基本 INSERT、 UPDATE 及 DELETE 陳述式就能提升效能及可調適性。When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. 例如: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);  

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

[ 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> ::=  
    { [ NOT ] <predicate> | ( <search_condition> ) }   
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ]   
[ ,...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 ) }   

<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  

引數Arguments

WITH <common_table_expression>WITH <common_table_expression>
指定在 MERGE 陳述式範圍內定義的暫存具名結果集或檢視表,也稱為通用資料表運算式。Specifies the temporary named result set or view, also known as common table expression, defined within the scope of the MERGE statement. 結果集是從簡單查詢衍生而來,由 MERGE 陳述式參考。The result set is derived from a simple query and is referenced by the MERGE statement. 如需詳細資訊,請參閱 WITH common_table_expression (Transact-SQL)For more information, see WITH common_table_expression (Transact-SQL).

TOP ( expression ) [ PERCENT ]TOP ( expression ) [ PERCENT ]
指定受到影響的資料列數目或百分比。Specifies the number or percentage of rows that are affected. expression 可以是一個數字,也可以是資料列的百分比。expression can be either a number or a percentage of the rows. TOP 運算式中參考的資料列並不會依任何順序排列。The rows referenced in the TOP expression are not arranged in any order. 如需詳細資訊,請參閱 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

當整個來源資料表和整個目標資料表聯結在一起,而且不符合插入、更新或刪除動作的聯結資料列移除之後,才會套用 TOP 子句。The TOP clause is applied after the entire source table and the entire target table are joined and the joined rows that do not qualify for an insert, update, or delete action are removed. TOP 子句會進一步將聯結的資料列數減少為指定的值,而且插入、更新或刪除動作會依照未排序的方式套用到剩餘的聯結資料列。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. 也就是說,將資料列散發到 WHEN 子句中定義的動作時,沒有任何特定順序。That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. 例如,指定 TOP (10) 會影響 10 個資料列;在這些資料列中,可能會更新 7 個及插入 3 個,或者可能會刪除 1 個、更新 5 個及插入 4 個,依此類推。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.

因為 MERGE 陳述式會針對來源和目標資料表執行完整資料表掃描,所以當使用 TOP 子句,藉由建立多個批次來修改大型資料表時,I/O 效能可能會受到影響。Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. 在此狀況中,請務必確保所有後續批次都是以新的資料列為目標。In this scenario, it is important to ensure that all successive batches target new rows.

database_namedatabase_name
這是 target_table 所在的資料庫名稱。Is the name of the database in which target_table is located.

schema_nameschema_name
這是 target_table 所屬的結構描述名稱。Is the name of the schema to which target_table belongs.

target_tabletarget_table
這是資料表或檢視表,<table_source> 的資料列會根據 <clause_search_condition>,對此資料表或檢視進行比對。Is the table or view against which the data rows from <table_source> are matched based on <clause_search_condition>. target_table 是 MERGE 陳述式的 WHEN 子句所指定之任何插入、更新或刪除作業的目標。target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

如果 target_table 是檢視,則對其進行的任何動作都必須滿足更新檢視的條件。If target_table is a view, any actions against it must satisfy the conditions for updating views. 如需詳細資訊,請參閱透過檢視修改資料For more information, see Modify Data Through a View.

target_table 不能是遠端資料表。target_table cannot be a remote table. target_table 不能有任何定義的規則。target_table cannot have any rules defined on it.

[ AS ] table_alias[ AS ] table_alias
這是用於參考資料表的替代名稱。Is an alternative name used to reference a table.

USING <table_source>USING <table_source>
指定根據 <merge_search condition>,與 target_table 的資料列進行比對的資料來源。Specifies the data source that is matched with the data rows in target_table based on <merge_search condition>. 此項比對的結果會指定 MERGE 陳述式的 WHEN 子句所採取的動作。The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. <table_source> 可以是遠端資料表或存取遠端資料表的衍生資料表。<table_source> can be a remote table or a derived table that accesses remote tables.

<table_source> 可以是使用 Transact-SQLTransact-SQL 資料表值建構函式的衍生資料表,藉由指定多個資料列來建構資料表。<table_source> can be a derived table that uses the Transact-SQLTransact-SQL table value constructor to construct a table by specifying multiple rows.

如需有關此子句語法和引數的詳細資訊,請參閱 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>
指定條件,在這些條件下,<table_source> 會與 target_table 聯結以決定其相符之處。Specifies the conditions on which <table_source> is joined with target_table to determine where they match.

警告

請務必只從目標資料表指定用於比對用途的資料行;It is important to specify only the columns from the target table that are used for matching purposes. 也就是說,從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。That is, specify columns from the target table that are compared to the corresponding column of the source table. 請勿嘗試在 ON 子句中篩選出目標資料表的資料列 (例如指定 AND NOT target_table.column_x = value) 來改善查詢效能。Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. 這樣做可能會傳回非預期且不正確的結果。Doing so may return unexpected and incorrect results.

WHEN MATCHED THEN <merge_matched>WHEN MATCHED THEN <merge_matched>
指定所有符合 <table_source> ON <merge_search_condition> 傳回的資料列且滿足任何其他搜尋條件的 target_table 資料列,都會根據 <merge_matched> 子句更新或刪除。Specifies that all rows of target_table that 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.

MERGE 陳述式最多可以具有兩個 WHEN MATCHED 子句。The MERGE statement can have at most two WHEN MATCHED clauses. 如果指定兩個子句,則第一個子句必須附帶 AND <search_condition> 子句。If two clauses are specified, then the first clause must be accompanied by an AND <search_condition> clause. 對於任何給定資料列,只有第一個 WHEN MATCHED 子句未套用時,才會套用第二個 WHEN MATCHED 子句。For any given row, the second WHEN MATCHED clause is only applied if the first is not. 如果有兩個 WHEN MATCHED 子句,則一個必須指定 UPDATE 動作,另一個則必須指定 DELETE 動作。If there are two WHEN MATCHED clauses, then one must specify an UPDATE action and one must specify a DELETE action. 如果在 <merge_matched> 子句中指定 UPDATE,而且根據 <merge_search_condition>,有一個以上的 <table_source> 資料列符合 target_table 的資料列,則 [SQL Server]SQL Server 會傳回錯誤。If 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 Server]SQL Server returns an error. MERGE 陳述式無法更新同一資料列一次以上或更新及刪除同一資料列。The MERGE statement cannot 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>
指定由 <table_source> ON <merge_search_condition> 傳回的每一資料列若不符合 target_table 的資料列但卻滿足其他的搜尋條件 (若存在),就會在 target_table 中插入一個資料列。Specifies that a row is inserted into target_table for every row returned by <table_source> ON <merge_search_condition> that does not match a row in target_table, but does satisfy an additional search condition, if present. 插入的值是由 <merge_not_matched> 子句決定。The values to insert are specified by the <merge_not_matched> clause. MERGE 陳述式只能具有一個 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>
指定所有不符合 <table_source> ON <merge_search_condition> 傳回的資料列,卻能滿足任何其他搜尋條件的 target_table 資料列,都會根據 <merge_matched> 子句更新或刪除。Specifies that all rows of target_table that do not match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition, are either updated or deleted according to the <merge_matched> clause.

MERGE 陳述式最多可以具有兩個 WHEN NOT MATCHED BY SOURCE 子句。The MERGE statement can have at most two WHEN NOT MATCHED BY SOURCE clauses. 如果指定兩個子句,則第一個子句必須附帶 AND <clause_search_condition> 子句。If two clauses are specified, then the first clause must be accompanied by an AND <clause_search_condition> clause. 對於任何給定資料列,只有第一個 WHEN NOT MATCHED BY SOURCE 子句未套用時,才會套用第二個 WHEN NOT MATCHED BY SOURCE 子句。For any given row, the second WHEN NOT MATCHED BY SOURCE clause is only applied if the first is not. 如果有兩個 WHEN NOT MATCHED BY SOURCE 子句,則一個必須指定 UPDATE 動作,另一個則必須指定 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. 只有目標資料表中的資料行才可以在 <clause_search_condition> 中參考。Only columns from the target table can be referenced in <clause_search_condition>.

當 <table_source> 未傳回任何資料列時,就無法存取來源資料表的資料行。When no rows are returned by <table_source>, columns in the source table cannot be accessed. 如果 <merge_matched> 子句所指定的更新或刪除動作參考了來源資料表的資料行,就會傳回錯誤 207 (資料行名稱無效)。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. 例如,子句 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 可能會導致陳述式失敗,因為無法存取來源資料表的 Col1For 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>
指定任何有效的搜尋條件。Specifies any valid search condition. 如需詳細資訊,請參閱搜尋條件 (Transact-SQL)For more information, see Search Condition (Transact-SQL).

<table_hint_limited><table_hint_limited>
指定針對每個由 MERGE 陳述式所執行的插入、更新或刪除動作,套用到目標資料表的一個或多個資料表提示。Specifies one or more table hints that are applied on the target table for each of the insert, update, or delete actions that are performed by the MERGE statement. WITH 關鍵字和括號都是必要的。The WITH keyword and the parentheses are required.

不允許使用 NOLOCK 和 READUNCOMMITTED。NOLOCK and READUNCOMMITTED are not allowed. 如需資料表提示的詳細資訊,請參閱資料表提示 (Transact-SQL)For more information about table hints, see Table Hints (Transact-SQL).

指定 INSERT 陳述式目標資料表之 TABLOCK 提示的效果,與指定 TABLOCKX 提示相同。Specifying the TABLOCK hint on a table that is the target of an INSERT statement has the same effect as specifying the TABLOCKX hint. 獨佔鎖定是在資料表上取得的。An exclusive lock is taken on the table. 有指定 FORCESEEK 時,該提示會套用到與來源資料表聯結之目標資料表的隱含執行個體。When FORCESEEK is specified, it is applied to the implicit instance of the target table joined with the source table.

警告

使用 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 指定 READPAST 可能會導致違反 UNIQUE 條件約束的 INSERT 作業。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 ] )
在目標資料表上指定一個或多個索引的名稱或識別碼,以用於與來源資料表執行隱含聯結。Specifies the name or ID of one or more indexes on the target table for performing an implicit join with the source table. 如需詳細資訊,請參閱資料表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

<output_clause><output_clause>
針對 target_table 中每個更新、插入或刪除的資料列傳回一個資料列 (不依特定順序)。Returns a row for every row in target_table that is updated, inserted, or deleted, in no particular order. 您可以在輸出子句中指定 $action$action can be specified in the output clause. $actionnvarchar(10) 類型的資料行,會針對每一個資料列傳回以下三個值的其中一個:'INSERT'、'UPDATE' 或 'DELETE' (根據該資料列上執行的動作而定)。$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 that was performed on that row. 如需有關此子句引數的詳細資訊,請參閱 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 ] )
指定利用最佳化工具提示來自訂 Database Engine 處理陳述式的方式。Specifies that optimizer hints are used to customize the way the Database Engine processes the statement. 如需詳細資訊,請參閱查詢提示 (Transact-SQL)For more information, see Query Hints (Transact-SQL).

<merge_matched><merge_matched>
指定所有不符合 <table_source> ON <merge_search_condition> 傳回的資料列、但卻滿足任何其他搜尋條件的 target_table 資料列,所會套用的更新或刪除動作。Specifies the update or delete action that is applied to all rows of target_table that do not match the rows returned by <table_source> ON <merge_search_condition>, and that satisfy any additional search condition.

UPDATE SET <set_clause>UPDATE SET <set_clause>
指定要在目標資料表中更新的資料行或變數名稱清單,以及用於更新這些名稱的值。Specifies the list of column or variable names to be updated in the target table and the values with which to update them.

如需有關此子句引數的詳細資訊,請參閱 UPDATE (Transact-SQL)For more information about the arguments of this clause, see UPDATE (Transact-SQL). 不允許將變數設定為與資料行相同的值。Setting a variable to the same value as a column is not permitted.

DeleteDELETE
指定符合 target_table 資料列的資料列會遭到刪除。Specifies that the rows matching rows in target_table are deleted.

<merge_not_matched><merge_not_matched>
指定要插入目標資料表的值。Specifies the values to insert into the target table.

(column_list)(column_list)
這是要插入資料的一個或多個目標資料表資料行的清單。Is a list of one or more columns of the target table in which to insert data. 必須將資料行指定為單一部分名稱,否則 MERGE 陳述式會失敗。Columns must be specified as a single-part name or else the MERGE statement will fail. column_list 必須以括弧括住,並以逗號分隔。column_list must be enclosed in parentheses and delimited by commas.

VALUES ( values_list)VALUES ( values_list)
這是以逗號分隔的常數、變數或運算式清單,這些項目會傳回要插入目標資料表的值。Is a comma-separated list of constants, variables, or expressions that return values to insert into the target table. 運算式不能包含 EXECUTE 陳述式。Expressions cannot contain an EXECUTE statement.

DEFAULT VALUESDEFAULT VALUES
強制插入的資料列包含定義給每個資料行的預設值。Forces the inserted row to contain the default values defined for each column.

如需此子句的詳細資訊,請參閱 INSERT (Transact-SQL)For more information about this clause, see INSERT (Transact-SQL).

<search condition><search condition>
指定用來指定 <merge_search_condition> 或 <clause_search_condition> 的搜尋條件。Specifies the search conditions used to specify <merge_search_condition> or <clause_search_condition>. 如需有關此子句之引數的詳細資訊,請參閱搜尋條件 (Transact-SQL)For more information about the arguments for this clause, see Search Condition (Transact-SQL).

RemarksRemarks

必須至少指定三個 MATCHED 子句中的一個,但可依任何順序指定這些子句。At least one of the three MATCHED clauses must be specified, but they can be specified in any order. 在同一個 MATCHED 子句中,不能更新變數一次以上。A variable cannot be updated more than once in the same MATCHED clause.

在目標資料表上由 MERGE 陳述式所指定的任何插入、更新或刪除動作,都受限於資料表上定義的任何條件約束,包括任何串聯式參考完整性條件約束。Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints. 如果在目標資料表上將任何唯一索引的 IGNORE_DUP_KEY 設定為 ON,則 MERGE 會忽略此設定。If IGNORE_DUP_KEY is set to ON for any unique indexes on the target table, MERGE ignores this setting.

MERGE 陳述式需要使用分號 (;) 做為陳述式結束字元。The MERGE statement requires a semicolon (;) as a statement terminator. 若 MERGE 陳述式執行時缺少該結束字元,就會引發錯誤 10713。Error 10713 is raised when a MERGE statement is run without the terminator.

如果用在 MERGE 之後,@@ROWCOUNT (Transact-SQL) 會將插入、更新和刪除的資料列總數傳回用戶端。When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

當資料庫相容性層級設定為 100 或更高時,MERGE 是完全保留的關鍵字。MERGE is a fully reserved keyword when the database compatibility level is set to 100 or higher. 雖然 MERGE 陳述式也可以在 90 和 100 資料庫相容性層級底下使用,但是當資料庫相容性層級設定為 90 時,此關鍵字並不會完全保留。The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.

使用佇列更新複寫時,不應該使用 MERGE 陳述式。The MERGE statement should not be used when using queued updating replication. MERGE 與佇列更新觸發程序不相容。The MERGE and queued updating trigger are not compatible. 請將 MERGE 陳述式取代成 Insert 或 Update 陳述式。Replace the MERGE statement with an insert or an update statement.

觸發程序實作Trigger Implementation

[SQL Server]SQL Server 會針對 MERGE 陳述式中指定的每個插入、更新或刪除動作,引發目標資料表上定義的對應 AFTER 觸發程序,但並不能保證哪一個動作會最先或最後引發觸發程序。For every insert, update, or delete action specified in the MERGE statement, [SQL Server]SQL Server fires any corresponding AFTER triggers defined on the target table, but does not guarantee on which action to fire triggers first or last. 為相同動作所定義的觸發程序會接受您指定的順序。Triggers defined for the same action honor the order you specify. 如需有關設定觸發程序引發順序的詳細資訊,請參閱指定第一個及最後一個觸發程序For more information about setting trigger firing order, see Specify First and Last Triggers.

如果針對 MERGE 陳述式所執行的插入、更新或刪除動作在目標資料表上定義啟用的 INSTEAD OF 觸發程序,則 MERGE 陳述式中指定的所有動作在目標資料表上都必須啟用 INSTEAD OF 觸發程序。If the target table has an enabled INSTEAD OF trigger defined on it for an insert, update, or delete action performed by a MERGE statement, then it must have an enabled INSTEAD OF trigger for all of the actions specified in the MERGE statement.

如果在 target_table 上定義了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 觸發程序,則不會執行更新或刪除作業。If there are any INSTEAD OF UPDATE or INSTEAD OF DELETE triggers defined on target_table, the update or delete operations are not performed. 反而會引發觸發程序,並據此填入 inserteddeleted 資料表。Instead, the triggers fire and the inserted and deleted tables are populated accordingly.

如果在 target_table 上定義任何 INSTEAD OF INSERT 觸發程序,則不會執行插入作業。If there are any INSTEAD OF INSERT triggers defined on target_table, the insert operation is not performed. 反而會引發觸發程序,並據此填入 inserted 資料表。Instead, the triggers fire and the inserted table is populated accordingly.

[權限]Permissions

來源資料表需要 SELECT 權限,目標資料表則需要 INSERT、UPDATE 或 DELETE 權限。Requires SELECT permission on the source table and INSERT, UPDATE, or DELETE permissions on the target table. 如需詳細資訊,請參閱 SELECTINSERTUPDATEDELETE 主題中的<權限>一節。For additional information, see the Permissions section in the SELECT, INSERT, UPDATE, and DELETE topics.

範例Examples

A.A. 以單一陳述式使用 MERGE 在資料表上執行 INSERT 和 UPDATE 作業Using MERGE to perform INSERT and UPDATE operations on a table in a single statement

如果符合的資料列存在,常見的狀況是在資料表中更新一個或多個資料行;如果符合的資料列不存在,則將資料當做新資料列插入。A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. 這通常是透過將參數傳遞到包含適當 UPDATE 和 INSERT 陳述式的預存程序來完成。This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. 您可以利用 MERGE 陳述式,在單一陳述式中同時執行兩個工作。With the MERGE statement, you can perform both tasks in a single statement. 以下範例示範 AdventureWorks2012AdventureWorks2012 資料庫中同時包含 INSERT 陳述式和 UPDATE 陳述式的預存程序。The following example shows a stored procedure in the AdventureWorks2012AdventureWorks2012database that contains both an INSERT statement and an UPDATE statement. 接著,程序會經過修改,以便使用單一 MERGE 陳述式來執行等同的作業。The procedure is then modified to perform 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. 以單一陳述式使用 MERGE 在資料表上執行 UPDATE 和 DELETE 作業Using MERGE to perform UPDATE and DELETE operations on a table in a single statement

下列範例使用 MERGE,根據在 ProductInventory 資料表中處理的順序,每日更新 AdventureWorks2012AdventureWorks2012 範例資料庫中的 SalesOrderDetail 資料表。The following example uses MERGE to update the ProductInventory table in the AdventureWorks2012AdventureWorks2012 sample database on a daily basis, based on orders that are processed in the SalesOrderDetail table. Quantity 資料表中的 ProductInventory 資料行會藉著減去 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. 如果產品的訂單數量使產品的存貨降為 0 或 0 以下,該產品的資料列就會從 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. 使用 MERGE 在目標資料表上透過衍生式來源資料表執行 UPDATE 和 INSERT 作業Using MERGE to perform UPDATE and INSERT operations on a target table by using a derived source table

以下範例會使用 MERGE,藉由更新或插入資料列來修改 AdventureWorks2012AdventureWorks2012 資料庫中的 SalesReason 資料表。The following example uses MERGE to modify the SalesReason table in the AdventureWorks2012AdventureWorks2012 database by either updating or inserting rows. 當來源資料表中的 NewName 值符合目標資料表 (Name) 中 SalesReason 資料行內的值時,就會更新目標資料表中的 ReasonType 資料行。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. NewName 的值不相符時,來源資料列會插入目標資料表中。When the value of NewName does not match, the source row is inserted into the target table. 來源資料表是一種衍生資料表,可使用 Transact-SQLTransact-SQL 資料表值建構函式針對來源資料表指定多個資料列。The source table is a derived table that uses the Transact-SQLTransact-SQL table value constructor to specify multiple rows for the source table. 如需有關在衍生資料表中使用資料表值建構函式的詳細資訊,請參閱資料表值建構函式 (Transact-SQL)For more information about using the table value constructor in a derived table, see Table Value Constructor (Transact-SQL). 這個範例也示範如何將 OUTPUT 子句的結果儲存在資料表變數中,然後摘要列出 MERGE 陳述式的結果,其方式是執行簡單的選取作業來傳回已插入和更新的資料列計數。The example also shows how to store the results of the OUTPUT clause in a table variable and then summarize the results of the MERGE statment by performing 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. 將 MERGE 陳述式的結果插入另一個資料表Inserting the results of the MERGE statement into another table

下列範例將擷取從 MERGE 陳述式的 OUTPUT 子句中傳回的資料,並將該資料插入另一個資料表中。The following example captures data returned from the OUTPUT clause of a MERGE statement and inserts that data into another table. MERGE 陳述式會根據在 Quantity 資料表中處理的順序,來更新 AdventureWorks2012AdventureWorks2012 資料庫中 ProductInventory 資料表的 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. 此範例會擷取已更新的資料列,並將其插入另一個資料表,該資料表是用於追蹤存貨變更。The example captures the rows that are updated and inserts them into another table that is 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  

另請參閱See Also

SELECT (Transact-SQL) SELECT (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
DELETE (Transact-SQL) DELETE (Transact-SQL)
OUTPUT 子句 (Transact-SQL) OUTPUT Clause (Transact-SQL)
Integration Services 套件中的 MERGE MERGE in Integration Services Packages
FROM (Transact-SQL) FROM (Transact-SQL)
資料表值建構函式 (Transact-SQL)Table Value Constructor (Transact-SQL)