合併 （Transact-SQL）

適用於：SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics（僅限專用 SQL 池）Microsoft Fabric 中的 SQL 資料庫Microsoft Fabric 中的倉庫

陳述 MERGE 式會從與來源資料表聯結的結果，在目標資料表上執行插入、更新或刪除作業。 例如，根據在另一個資料表中所找到的差異在資料表中插入、更新或刪除資料列，以同步處理兩個資料表。

本文根據所選產品版本提供不同的語法、引數、備註、權限和範例。 從版本下拉式清單中選取您想要的產品版本。

Transact-SQL 語法慣例

Syntax

SQL Server 和 Azure SQL Database 的語法：

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

Arguments

與 <common_table_expression>

指定在陳述式範圍內 MERGE 定義的暫存具名結果集或檢視，也稱為通用資料表運算式。 結果集衍生自簡單查詢，並由陳述式參考 MERGE 。 如需詳細資訊，請參閱 WITH common_table_expression (Transact-SQL)。

TOP （ 運算式 ） [ PERCENT ]

指定受到影響的資料列數目或百分比。 expression 可以是一個數字，也可以是資料列的百分比。 運算式中 TOP 參考的資料列不會以任何順序排列。 如需詳細資訊，請參閱 TOP (Transact-SQL)。

此 TOP 子句會在移除整個來源資料表和整個目標資料表聯結，以及不符合插入、更新或刪除動作資格的聯結資料列之後套用。 子句會 TOP 進一步將聯結列數減少至指定的值。 這些動作（插入、更新或刪除）會以未排序的方式套用至其餘聯結的數據列。 也就是說，列在子句中 WHEN 定義的動作之間沒有分配的順序。 例如，指定 TOP （10） 會影響 10 列。 在這些數據列中，7 個可能會更新，3 個插入，或 1 個可能會刪除、5 個更新和 4 個插入等等。

如果來源表格上沒有過濾器， MERGE 陳述式可能會在來源表格上執行表格掃描或叢集索引掃描，以及目標表格的表格掃描或叢集索引掃描。 因此，即使使用 TOP 子句透過建立多個批次來修改大型資料表，I/O 效能有時也會受到影響。 在此狀況中，請務必確保所有後續批次都以新的資料列為目標。

database_name

target_table 所在的資料庫名稱。

schema_name

target_table 所屬的結構描述名稱。

target_table

根據比對來自 <table_source><clause_search_condition>之數據列的數據表或檢視表。 target_table 是陳述式子WHEN句所MERGE指定的任何插入、更新或刪除作業的目標。

如果 target_table 是檢視，則對其進行的任何動作都必須滿足更新檢視的條件。 如需詳細資訊，請參閱透過檢視修改資料。

target_table 不能是遠端資料表。 target_table無法定義任何規則。 target_table不能是記憶體優化數據表。

提示可以指定為 <merge_hint>。

[ 作為 ] table_alias

用於參考 target_table 資料表的替代名稱。

使用 <table_source>

根據 ，指定與 target_table列相符的數據源。 此比對的結果決定了陳述式的WHEN子句要MERGE採取的行動。 <table_source> 可以是遠端數據表或存取遠端數據表的衍生數據表。

<table_source> 可以是使用 Transact-SQL 資料表值建構函式，藉由指定多個數據列來建構 數據表的衍生數據表。

[ 作為 ] table_alias

用於參考 table_source 資料表的替代名稱。

如需有關此子句語法和引數的詳細資訊，請參閱 FROM (Transact-SQL)。

在merge_search_condition上<>

指定與 target_table<table_source>以判斷其相符位置的條件。

Caution

請務必只從目標資料表指定用於比對用途的資料行。 也就是說，從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。 請勿嘗試透過篩選出子句中 ON 目標資料表中的資料列來改善查詢效能，例如指定 AND NOT target_table.column_x = value。 這樣做可能會傳回非預期且不正確的結果。

比對時， <merge_matched>

指定符合 所傳回 <table_source> ON <merge_search_condition>列並滿足任何其他搜尋條件的所有 *target_table 列，都會根據子句來 <merge_matched> 更新或刪除。

陳述 MERGE 式最多可以有兩個 WHEN MATCHED 子句。 如果指定了兩個子句，則第一個子句必須伴隨一個 AND<search_condition> 子句。 對於任何指定的資料列，只有在第一個子句未適用時，才會套用第二個 WHEN MATCHED 子句。 如果有兩個 WHEN MATCHED 子句，則一個必須指定 UPDATE 動作，另一個必須指定 DELETE 動作。 When UPDATE 在子句中 <merge_matched> 指定，且多列 符合 <table_source>target_table 中的 <merge_search_condition>資料列，SQL Server 會傳回錯誤。 MERGE陳述式無法多次更新相同的資料列，或更新和刪除相同的資料列。

當不符合 [依目標] 時，則 <merge_not_matched>

指定針對傳回的每個資料列插入<table_source> ON <merge_search_condition>，該資料列不符合 target_table 中的資料列，但滿足其他搜尋條件 （如果存在的話）。 要插入的值是由 <merge_not_matched> 子句所指定。 陳述 MERGE 式只能有一個 WHEN NOT MATCHED [ BY TARGET ] 子句。

當來源不相符時，merge_matched <>

指定所有 *target_table 列 （不符合 所傳回 <table_source> ON <merge_search_condition>的資料列） 且滿足任何其他搜尋條件，都會根據子句進行 <merge_matched> 更新或刪除。

陳述 MERGE 式最多可以有兩個 WHEN NOT MATCHED BY SOURCE 子句。 如果指定兩個子句，則第一個子句必須伴隨子 AND<clause_search_condition> 句。 對於任何指定的資料列，只有在第一個子句未適用時，才會套用第二個 WHEN NOT MATCHED BY SOURCE 子句。 如果有兩個 WHEN NOT MATCHED BY SOURCE 子句，則一個必須指定一個 UPDATE 動作，一個必須指定 DELETE 一個動作。 只有目標數據表中的數據行可以在 中 <clause_search_condition>參考。

若未傳 <table_source>回任何數據列，則無法存取源數據表中的數據行。 如果 子句中指定的 <merge_matched> 更新或刪除動作參考源數據表中的數據行，則會傳回錯誤 207（無效的數據行名稱）。 例如，子句 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 可能會導致 語句失敗，因為在 Col1 源數據表中無法存取。

和 <clause_search_condition>

指定任何有效的搜尋條件。 如需詳細資訊，請參閱搜尋條件 （Transact-SQL）。

<table_hint_limited>

指定一或多個表格提示，以套用至目標表格，以針對陳述式所 MERGE 執行的每一個插入、更新或刪除動作。 WITH關鍵字和括號是必需的。

NOLOCK 並且 READUNCOMMITTED 是不允許的。 如需表格提示的詳細資訊，請參閱表格提示 （Transact-SQL）。

在作為陳述式目標TABLOCK的資料表上指定提示，INSERT與指定TABLOCKX提示具有相同的效果。 獨佔鎖定是在資料表上取得的。 當指定 FORCESEEK 時，該提示會套用到與來源資料表聯結之目標資料表的隱含執行個體。

Caution

指定 with READPAST 可能會導致WHEN NOT MATCHED [ BY TARGET ] THEN INSERTINSERT違反條件約束的UNIQUE作業。

INDEX （ index_val [ ,...n ] )

在目標資料表上指定一或多個索引的名稱或識別碼，以用於與來源資料表執行隱含聯結。 如需詳細資訊，請參閱資料表提示 (Transact-SQL)。

<output_clause>

針對 target_table 中每個更新、插入或刪除的資料列傳回一個資料列 (不依特定順序)。 $action 可以在 output 子句中指定。 $action是 nvarchar（10） 類型的 此OUTPUT子句是查詢或計算受 .MERGE 如需此子句引數及行為的詳細資訊，請參閱 OUTPUT 子句 （Transact-SQL）。

OPTION （ <query_hint> [ ,...n ] )

指定利用最佳化工具提示來自訂 Database Engine 處理陳述式的方式。 如需詳細資訊，請參閱查詢提示 （Transact-SQL）。

<merge_matched>

指定套用至與 傳回的資料列不相符的所有<table_source> ON <merge_search_condition>資料列的更新或刪除動作，且符合任何其他搜尋條件。

更新集 <set_clause>

指定要在目標資料表中更新的資料行或變數名稱清單，以及用於更新這些名稱的值。

如需有關此子句引數的詳細資訊，請參閱 UPDATE (Transact-SQL)。 不支援將變數設定為與資料行相同的值。

DELETE

指定符合 target_table 資料列的資料列會遭到刪除。

<merge_not_matched>

指定要插入目標資料表的值。

（ column_list ）

要插入資料的一或多個目標資料表資料行清單。 直欄必須指定為單一部分名稱，否則 MERGE 陳述式會失敗。 column_list 必須以括弧括住，並以逗號分隔。

VALUES ( values_list)

以逗號分隔的常數、變數或運算式清單，這些項目會傳回要插入至目標資料表的值。 運算式不能包含 EXECUTE 陳述式。

預設值

強制插入的資料列包含定義給每個資料行的預設值。

如需此子句的詳細資訊，請參閱 INSERT (Transact-SQL)。

<search_condition>

指定要指定 <merge_search_condition> 或 <clause_search_condition>的搜尋條件。 如需此子句引數的詳細資訊，請參閱 搜尋條件 （Transact-SQL）。

<圖形搜尋模式>

指定圖形搜尋模式。 如需這個子句自變數的詳細資訊，請參閱 MATCH （Transact-SQL） 。

Remarks

當兩個資料表具有比對性質的複雜混合時，陳述式所描述 MERGE 的條件式行為最有效。 例如沒有資料列時插入資料列，或資料列相符時更新資料列。 當簡單地根據另一個表的資料列更新一個表時，請使用 、 INSERT和UPDATE陳述式來DELETE改善效能和可擴展性。 例如：

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

必須指定三個 MATCHED 子句中的至少一個，但可以以任何順序指定它們。 變數不能在同一子句中 MATCHED 更新多次。

陳述式在 MERGE 目標表格上指定的任何插入、更新或刪除動作都會受到其上定義的任何限制，包括任何階層式參照完整性限制。 如果是IGNORE_DUP_KEY目標資料表上的任何唯一索引，ON則MERGE忽略此設定。

陳述式需要 MERGE 分號 （;） 作為陳述式終止符。 當在沒有終止符的情況下執行陳述式時 MERGE ，會引發錯誤 10713。

在之後 MERGE使用時， @@ROWCOUNT （Transact-SQL） 會傳回插入、更新和刪除給用戶端的列總數。

MERGE 是資料庫相容性層級設定為 100 或更高時完全保留的關鍵字。 陳述MERGE式可在 和 90 資料庫相容性層級下100使用;不過，當資料庫相容性層級設定為 90時，關鍵字不會完全保留。

Caution

使用MERGE時，請勿使用陳述式。 MERGE和佇列更新觸發程式不相容。 將陳述 MERGE 式 INSERT 取代為 and UPDATE 陳述式。

觸發器實作

針對陳述式中 MERGE 指定的每個插入、更新或刪除動作，SQL Server 會引發目標資料表上定義的任何對應 AFTER 觸發程序，但不保證要先或最後觸發觸發程序的動作。 為相同動作所定義的觸發程序會接受您指定的順序。 如需有關設定觸發程序引發順序的詳細資訊，請參閱指定第一個及最後一個觸發程序。

如果目標表格上針對陳述式所INSTEAD執行的插入、更新或刪除動作定義了已啟用MERGE的 OF 觸發程式，則它必須針對陳述式中INSTEAD指定的所有動作具有已啟用MERGE的 OF 觸發程式。

如果INSTEAD上定義任何 UPDATE OF INSTEAD 或 DELETE OF 觸發程序，則不會執行更新或刪除作業。 會改為引發觸發程序，並據此填入 inserted 和 deleted 資料表。

如果在INSTEAD上定義任何 INSERT OF 觸發程序，則不會執行插入作業。 反之，會據此填入資料表。

Note

與個別 INSERT、 UPDATE和 DELETE 陳述式不同，觸發程式內部反映的資料 @@ROWCOUNT 列數目可能會更高。 內部任何@@ROWCOUNTAFTER觸發器（無論觸發器捕獲的資料修改語句為何）都會反映受 影響MERGE的列總數。 例如，如果陳述 MERGE 式插入一列、更新一列並刪除一列， @@ROWCOUNT 則任何 AFTER 觸發器都會為 3，即使觸發器僅針對陳述式宣 INSERT 告。

Permissions

需要SELECT來源資料表的許可權和 INSERT、 或UPDATEDELETE目標資料表的許可權。 如需詳細資訊，請參閱 SELECT （Transact-SQL）、INSERT （Transact-SQL）、UPDATE （Transact-SQL） 和 DELETE （Transact-SQL） 文章中的 許可權 區段。

索引最佳做法

透過使用 MERGE 陳述式，您可以將個別 DML 陳述式取代為單一陳述式。 這樣可以提升查詢效能，因為作業會在單一陳述式內執行，因此會讓來源和目標資料表中的資料處理次數減至最少。 但是，效能改善取決於是否有正確的索引、聯結以及適當進行其他考量。

為了改善陳述式的 MERGE 效能，我們建議使用下列索引準則：

• 建立索引以促進來源與目標 MERGE之間的聯結：
  • 在來源資料表的聯結資料行上建立索引，其索引鍵涵蓋目標資料表的聯結邏輯。 可能的話，它應該是唯一的。
  • 此外，請在目標資料表的聯結資料行中建立索引。 可能的話，它應該是唯一的叢集索引。
  • 這兩個索引可確保資料表中的資料經過排序，而且唯一性有助於提升比較的效能。 查詢效能已經過改良，因為查詢最佳化工具不需要執行額外的驗證處理，也可以尋找及更新重複的資料列，而且不需要其他排序作業。
• 避免使用任何形式的資料行存放區索引作為陳述式目標 MERGE 的資料表。 如同任何 UPDATE，您可能會透過更新暫存的資料列存放區資料表，然後執行批次處理的 DELETE and INSERT，而不是 UPDATE 或 MERGE，來改善資料行存放區索引的效能。

MERGE 的並行考慮

在鎖定方面， MERGE 不同於離散、連續 INSERT、 UPDATE、 DELETE 和語句。 MERGE 仍然執行 INSERT、 UPDATE和 DELETE 操作，但使用不同的鎖定機制。 針對某些應用程式需求，撰寫離散 INSERT、 UPDATE和 DELETE 陳述式可能會更有效率。 大規模時， MERGE 可能會造成複雜的並行問題，或需要進階疑難排解。 因此，請規劃在部署至生產環境之前徹底測試任何 MERGE 陳述式。

MERGE 陳述式是離散 INSERT、 UPDATE和 DELETE 作業的適當替代品，在下列案例中 （但不限於）：

• 涉及大型資料列計數的 ETL 作業會在非預期其他並行作業時執行。 當預期大量並行時，個別 INSERT、 UPDATE和 DELETE 邏輯可能比陳述式執行 MERGE 得更好，且封鎖較少。
• 涉及小型資料列計數和交易的複雜作業不太可能在延長期間內執行。
• 涉及使用者資料表的複雜作業，其中索引可以設計為確保最佳執行計劃，避免資料表掃描和查閱，以利索引掃描，或最好是索引搜尋。

並行的其他考慮：

• 在某些案例中，唯一索引鍵預期會由 插入 MERGE和更新，指定 將 HOLDLOCK 防止唯一索引鍵違規。 HOLDLOCK 是交易隔離層級的 SERIALIZABLE 同義詞，它不允許其他並發交易修改此交易已讀取的資料。 SERIALIZABLE 是最安全的隔離層次，但提供與其他交易的最低並行性，該交易會保留資料範圍的鎖定，以防止在讀取進行時插入或更新虛擬列。 如需 的詳細資訊HOLDLOCK，請參閱 表格提示 和 SET TRANSACTION ISOLATION LEVEL （Transact-SQL）。

JOIN 最佳做法

為了改善陳述式的 MERGE 效能並確保獲得正確的結果，我們建議遵循下列聯結準則：

• 僅在子句中 ON <merge_search_condition> 指定搜尋條件，以決定比對來源和目標表格中資料的準則。 也就是說，只從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。
• 請勿包含與其他值 (如常數) 的比較。

若要從來源或目標資料表中篩選掉資料列，請使用下列其中一個方法。

• 在適當的 WHEN 子句中指定列過濾的搜尋條件。 例如， WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
• 在來源或目標上定義一個可傳回篩選過之資料列的檢視表，並將此檢視表當做來源或目標資料表來參考。 如果此檢視表定義在目標資料表上，則對其進行的任何動作都必須滿足更新檢視表的條件。 如需使用檢視更新資料的詳細資訊，請參閱 透過檢視修改資料。
• 使用 WITH <common table expression> 子句，從來源或目標資料表中篩選掉資料列。 此方法類似於在子句中 ON 指定其他搜尋準則，可能會產生不正確的結果。 我們建議您最好避免使用這個方法，或是在實作這個方法之前先徹底加以測試。

陳述式中的 MERGE 聯結作業會以與陳述式中的 SELECT 聯結相同的方式最佳化。 也就是說，當 SQL Server 處理聯結時，查詢最佳化工具將從多種可能性選擇最有效率的聯結處理方式。 當來源和目標的大小類似，而且先前所述的索引指導方針適用於來源和目標資料表時，合併聯結運算子就是最有效率的查詢計畫。 這是因為這兩個資料表都會掃描一次，而且不需要排序資料。 當來源小於目標資料表時，偏好的是巢狀迴圈運算子。

您可以在陳述式中OPTION (<query_hint>)指定MERGE子句，以強制使用特定聯結。 建議您不要使用雜湊聯結作為陳述式的 MERGE 查詢提示，因為此聯結類型不會使用索引。

參數化最佳做法

如果執行 、 SELECT、 INSERT或 UPDATE 陳述式時沒有參數，SQL DELETEServer 查詢最佳化工具可能會選擇在內部參數化陳述式。 這表示會以參數替代包含在查詢中的任何常值。 例如，語句 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)可以在內部實作為 INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2)。 這個程式稱為 簡單參數化，可增加關係型引擎將新的SQL語句與先前編譯的執行計劃比對的能力。 查詢效能可能會改善，因為查詢編譯和重新編譯的頻率會降低。 查詢最佳化工具不會將簡單的參數化程序套用至 MERGE 陳述式。 因此， MERGE 包含文字值的陳述式可能無法執行個別 INSERT、 UPDATE或 DELETE 陳述式，因為每次執行陳述式時 MERGE 都會編譯新計劃。

若要改善查詢效能，我們建議您遵循參數化指導方針：

• 參數化子句和陳述式子ON <merge_search_condition>句中的所有WHENMERGE文字值。 例如，您可以將陳述式併入 MERGE 預存程序，以適當的輸入參數取代常值。
• 如果您不能將此陳述式參數化，請建立 TEMPLATE 類型的計劃指南，並在此計劃指南中指定 PARAMETERIZATION FORCED 查詢提示。 如需詳細資訊，請參閱使用計劃指南指定查詢參數化行為。
• 如果在資料庫上頻繁執行陳述式，請 MERGE 考慮將資料庫上的選項設為 PARAMETERIZATIONFORCED。 當設定這個選項時，請務必小心。 PARAMETERIZATION 選項是資料庫層級設定，而且會影響對資料庫進行之所有查詢的處理方式。 如需詳細資訊，請參閱強制參數化。
• 作為計劃指南的較新且更簡單的替代方案，請考慮使用查詢存放區提示的類似策略。 如需詳細資訊，請參閱查詢存放區提示。

TOP 子句最佳做法

在陳述式中 MERGE ，子 TOP 句會指定在聯結來源資料表和目標資料表之後，以及移除不符合插入、更新或刪除動作資格的資料列之後，受影響的資料列數目或百分比。 此 TOP 子句會進一步將聯結列數減少至指定的值，且插入、更新或刪除動作會以未排序的方式套用至其餘聯結列。 也就是說，列在子句中 WHEN 定義的動作之間沒有分配的順序。 例如，指定 TOP (10) 會影響 10 列;在這些列中，可能會更新 7 列並插入 3 列，或可能會刪除 1 列、更新 5 列，以及插入 4 列，依此類推。

通常使用子 TOP 句在大型資料表上批次執行資料操作語言 （DML） 作業。 為此目的使用聲明中的TOP子句時MERGE，了解以下含義非常重要。

• I/O 效能可能會受到影響。

  陳述 MERGE 式會執行來源及目標表格的完整表格掃描。 將作業分割成批次可減少每個批次執行的寫入作業數目;不過，每個批次都會執行來源和目標數據表的完整數據表掃描。 產生的讀取活動可能會影響查詢的效能，以及數據表上的其他並行活動。

• 可能產生不正確的結果。

  請務必確定所有後續批次都是以新的資料列為目標，否則可能會發生不想要的行為，例如錯誤地將重複資料列插入目標資料表。 當來源資料表包含的資料列不在目標批次中，而是在整體目標資料表中時，可能會發生這個狀況。 若要確保結果正確：

  • 使用子 ON 句來判斷哪些來源列會影響現有的目標列，以及哪些是真正的新列。
  • 使用子句中的 WHEN MATCHED 其他條件來判斷目標列是否已由前一個批次更新。
  • 使用子句和WHEN MATCHED邏輯中的SET其他條件來驗證同一列無法更新兩次。

因為子 TOP 句只會在套用這些子句之後套用，所以每次執行都會插入一個真正不相符的資料列，或更新一個現有的資料列。

大量載入最佳做法

此 MERGE 陳述式可用來將子句指定 OPENROWSET(BULK...) 為表格來源，以有效率地將資料從來源資料檔大量載入至目標表格。 這樣做的話，整個檔案都會在單一批次中處理。

若要改善大量合併程序的效能，我們建議您遵循下列指導方針：

• 在目標資料表的聯結資料行上，建立叢集索引。

• 在大量載入 MERGE期間停用目標資料表上的其他非唯一、非叢集索引，之後再啟用它們。 這對於耗時的大量資料作業很常見且很有用。

• 使用子句中的 ORDER and UNIQUEOPENROWSET(BULK...) hints 來指定來源資料檔的排序方式。

  依預設，大量作業會假設資料檔沒有排序。 因此，來源資料必須根據目標資料表上的叢集索引進行排序，並 ORDER 使用提示來指出順序，以便查詢最佳化工具可以產生更有效率的查詢計劃。 提示會在執行階段驗證；如果串流不符合指定的提示，就會引發錯誤。

這些指導方針可確保聯結索引鍵是唯一的，而且來源檔案中的資料排序次序會符合目標資料表。 查詢效能會有所提升，因為不需要其他排序作業，而且也不需要不必要的資料複製。

測量和診斷 MERGE 效能

下列特性可協助您測量及診斷陳述式的 MERGE 效能。

• 在sys.dm_exec_query_optimizer_info動態管理視圖中使用合併 stmt 計數器，傳回陳述式的MERGE查詢最佳化數目。
• 使用merge_action_type動態管理視圖中的屬性，傳回作為陳述式結果MERGE所使用的觸發程式執行計劃類型。
• 使用「延伸事件階段作業」來收集陳述式的 MERGE 疑難排解資料，其方式與其他資料操作語言 （DML） 陳述式相同。 如需擴充事件概觀的詳細資訊，請參閱快速入門：擴充事件和使用 SSMS XEvent 分析工具。

Examples

A. 使用 MERGE 在單一語句中對數據表執行 INSERT 和 UPDATE 作業

如果存在相符的資料列，則常見情況是更新資料表中的一或多個資料行。 另一個情況是，如果沒有相符的資料列，則將資料作為新資料列插入。 您通常會將參數傳遞至包含適當 UPDATE and INSERT 陳述式的預存程序，以執行任一案例。 使用陳述 MERGE 式，您可以在單一陳述式中執行這兩項作業。 以下範例展示了 AdventureWorks2025 資料庫中的一個儲存程序，該程序同時包含一個 INSERT 語句與一個 UPDATE 語句。 然後修改程序，以使用單一 MERGE 陳述式來執行對等作業。

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');

DROP TABLE #MyTempTable;
GO

B. 使用 MERGE 在單一語句中對數據表執行 UPDATE 和 DELETE 作業

以下範例用於 MERGE 每日更新 ProductInventory AdventureWorks2025 範例資料庫中的表格，根據表格中 SalesOrderDetail 處理的訂單。 Quantity 資料表中的 ProductInventory 資料行會藉著減去 SalesOrderDetail 資料表中每個產品每日所下的訂單數量來進行更新。 如果產品的訂單數量使產品的存貨降為 0 或 0 以下，該產品的資料列就會從 ProductInventory 資料表中刪除。

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

EXECUTE Production.usp_UpdateInventory '20030501';

C. 使用 MERGE 在目標數據表上使用衍生源數據表執行 UPDATE 和 INSERT 作業

以下範例用於 MERGE 修改 SalesReason AdventureWorks2025 資料庫中的表格，方法是更新或插入資料列。

當來源資料表中的 NewName 值符合目標資料表 (Name) 中 SalesReason 資料行內的值時，就會更新目標資料表中的 ReasonType 資料行。 當 NewName 的值不相符時，來源資料列會插入目標資料表中。 來源資料表是一種衍生資料表，可使用 Transact-SQL 資料表值建構函式針對來源資料表指定多個資料列。 如需有關在衍生資料表中使用資料表值建構函式的詳細資訊，請參閱資料表值建構函式 (Transact-SQL)。

該OUTPUT子句可用於查詢陳述式的MERGE結果，有關更多信息，請參閱 OUTPUT 子句 （Transact-SQL）。 此範例也顯示如何將子句的 OUTPUT 結果儲存在表格變數中。 然後，您可以執行簡單的選取作業來摘要陳述式的 MERGE 結果，以傳回插入和更新的資料列計數。

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change,
    COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

D. 將 MERGE 語句的結果插入另一個數據表

下列範例會擷取從陳述式子OUTPUT句傳MERGE回的資料，並將該資料插入另一個資料表。 該MERGE語句會根據 AdventureWorks2025 資料庫中處理SalesOrderDetail的訂單，更新Quantity該表格的ProductInventory欄位。 此範例會擷取更新的資料列，並將其插入至另一個資料表，該資料表用於追蹤存貨變更。

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO

E. 使用 MERGE 在圖形資料庫中的目標邊緣資料表上執行 INSERT 或 UPDATE

在此範例中，您會建立 Person 和 City 節點資料表以及 livesIn 邊緣資料表。 您可以在邊緣上MERGE使用livesIn陳述式，如果邊緣在 和 PersonCity之間尚不存在，請插入新列。 如果邊緣已經存在，則您只需更新 livesIn 邊緣上的 StreetAddress 屬性。

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

相關內容

• 選擇 （Transact-SQL）
• 插入 （Transact-SQL）
• 更新 （Transact-SQL）
• 刪除 （Transact-SQL）
• OUTPUT 子句 （Transact-SQL）
• Integration Services 套件中的 MERGE
• FROM 子句與 JOIN、APPLY、PIVOT (Transact-SQL)
• 資料表值建構函式 (Transact-SQL)