MERGE (Transact-SQL)

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics

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

注意

如需 Azure Synapse Analytics 專屬的 MERGE 資訊，請將版本選取專案變更為 Azure Synapse Analytics。

提示

當兩個資料表有複雜的比對的特性時，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);

Transact-SQL 語法慣例

語法

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>

引數

WITH <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 是 MERGE 陳述式的 WHEN 子句所指定之任何插入、更新或刪除作業的目標。

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

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

提示可以指定為 <merge_hint>。

[ AS ] table_alias

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

USING <table_source>

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

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

[ AS ] table_alias

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

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

ON <merge_search_condition>

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

警告

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

WHEN MATCHED THEN <merge_matched>

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

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

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

指定 ON <merge_search_condition> 所<table_source>傳回之每一個數據列與target_table中不相符的數據列插入target_table，但如果存在，則會滿足其他搜尋條件。 要插入的值是由 <merge_not_matched> 子句所指定。 MERGE 陳述式只能具有一個 WHEN NOT MATCHED [ BY TARGET ] 子句。

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

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

MERGE 陳述式最多可以具有兩個 WHEN NOT MATCHED BY SOURCE 子句。 如果指定了兩個子句，則第一個子句必須隨附 AND <clause_search_condition> 子句。 對於任何指定的資料列，只有第一個 WHEN NOT MATCHED BY SOURCE 子句未套用時，才會套用第二個 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 源數據表中無法存取。

AND <clause_search_condition>

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

<table_hint_limited>

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

不允許使用 NOLOCK 和 READUNCOMMITTED。 如需資料表提示的詳細資訊，請參閱資料表提示 (Transact-SQL)。

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

警告

使用 WHEN NOT MATCHED [ BY TARGET ] 指定 READPAST，THEN INSERT 可能會導致 INSERT 作業違反 UNIQUE 條件約束。

INDEX ( index_val [ ,...n ] )

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

<output_clause>

針對 target_table 中每個更新、插入或刪除的資料列傳回一個資料列 (不依特定順序)。 $action 可以在 output 子句中指定。 $action是 nvarchar（10） 類型的資料行，會根據在該數據列上執行的動作，針對每個數據列傳回三個值之一：INSERT、 UPDATE或 DELETE。 查詢或計算受 MERGE 影響的資料列時，建議使用 OUTPUT 子句。 如需此子句的引數和行為詳細資訊，請參閱 OUTPUT 子句 (Transact-SQL)。

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

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

<merge_matched>

指定套用至不符合 ON <merge_search_condition>所傳回<table_source>之資料列之target_table之所有數據列的更新或刪除動作，並滿足任何其他搜尋條件。

UPDATE SET <set_clause>

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

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

刪除

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

<merge_not_matched>

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

（ column_list ）

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

VALUES ( values_list)

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

DEFAULT VALUES

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

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

<search_condition>

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

<圖形搜尋模式>

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

備註

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

在目標資料表上由 MERGE 陳述式所指定的任何插入、更新或刪除動作，都受限於資料表上定義的任何條件約束，包括任何串聯式參考完整性條件約束。 如果在目標資料表上將任何唯一索引的 IGNORE_DUP_KEY 設定為 ON，則 MERGE 會忽略此設定。

MERGE 陳述式需要使用分號 (;) 做為陳述式結束字元。 若 MERGE 陳述式執行時缺少該結束字元，就會引發錯誤 10713。

如果用在 MERGE 之後，@@ROWCOUNT (Transact-SQL) 會將插入、更新和刪除的資料列總數傳回用戶端。

當資料庫相容性層級設定為 100 或更新版本時，MERGE 是完全保留的關鍵詞。 MERGE 語句可在 和 100 資料庫相容性層級下90使用;不過，當資料庫相容性層級設定90為 時，關鍵詞不會完全保留。

警告

使用佇列更新複寫時，不應該使用 MERGE 陳述式。 MERGE 與佇列更新觸發程序不相容。 請將 MERGE 陳述式取代成 Insert 或 Update 陳述式。

觸發程序實作

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

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

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

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

注意

不同於個別的 INSERT、UPDATE 和 DELETE 語句，觸發程式內部@@ROWCOUNT所反映的數據列數目可能較高。 AFTER 觸發程序中的 @@ROWCOUNT，將不論觸發程序擷取的資料修改陳述式，而反映受 MERGE 影響的資料列數總計。 舉例來說，如果 MERGE 陳述式插入一個資料列、更新一個資料列並刪除一個資料列，那麼任一 AFTER 觸發程序的 @@ROWCOUNT 將會為三，即便觸發程序只針對 INSERT 陳述式宣告也是如此。

權限

來源資料表需要 SELECT 權限，目標資料表則需要 INSERT、UPDATE 或 DELETE 權限。 如需詳細資訊，請參閱 SELECT、INSERT、UPDATE 和 DELETE 主題中的＜權限＞一節。

索引最佳做法

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

若要改善 MERGE 陳述式的效能，我們建議您遵循索引指導方針：

• 建立索引以利合併來源與目標之間的聯結：
  • 在來源資料表的聯結資料行上建立索引，其索引鍵涵蓋目標資料表的聯結邏輯。 可能的話，它應該是唯一的。
  • 此外，請在目標資料表的聯結資料行中建立索引。 可能的話，它應該是唯一的叢集索引。
  • 這兩個索引可確保資料表中的資料經過排序，而且唯一性有助於提升比較的效能。 查詢效能已經過改良，因為查詢最佳化工具不需要執行額外的驗證處理，也可以尋找及更新重複的資料列，而且不需要其他排序作業。
• 請避免使用任何形式的資料行存放區索引作為 MERGE 陳述式目標的資料表。 如同任何 UPDATEE，您可以藉由更新分段數據列存放區數據表，然後執行批次的 DELETE 和 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 處理聯結時，查詢最佳化工具將從多種可能性選擇最有效率的聯結處理方式。 當來源和目標的大小類似，而且先前所述的索引指導方針適用於來源和目標資料表時，合併聯結運算子就是最有效率的查詢計畫。 這是因為這兩個資料表都會掃描一次，而且不需要排序資料。 當來源小於目標資料表時，偏好的是巢狀迴圈運算子。

您可以在 MERGE 陳述式中指定 OPTION (<query_hint>) 子句來強制使用特定聯結。 我們建議您不要針對 MERGE 陳述式使用雜湊聯結當做查詢提示，因為這個聯結類型不使用索引。

參數化最佳做法

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

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

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

TOP 子句最佳做法

在 MERGE 陳述式中，TOP 子句會指定在來源及目標資料表聯結後，以及在移除不符合插入、更新或刪除動作的資料列後，受影響之資料列的數目或百分比。 TOP 子句會進一步將聯結的資料列數減少為指定的值，而且插入、更新或刪除動作會依照未排序的方式套用到剩餘的聯結資料列。 亦即，將資料列散發到 WHEN 子句中定義的動作時，沒有任何特定順序。 例如，指定 TOP （10） 會影響 10 個數據列;在這些數據列中，7 個可能會更新，3 個插入，或者 1 個可能會刪除、5 個更新和 4 個插入等等。

使用 TOP 子句依照批次在大型資料表上執行資料操作語言 (DML) 作業是很常見的情形。 基於這個目的在 MERGE 陳述式中使用 TOP 子句時，了解以下含意是很重要的事情。

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

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

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

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

  • 使用 ON 子句來判斷哪些來源資料列會影響現有的全新目標資料列。
  • 使用 WHEN MATCHED 子句中的其他條件來判斷目標數據列是否已由上一個批次更新。
  • 使用 WHEN MATCHED 子句和 SET 邏輯中的其他條件來驗證無法更新相同的資料列兩次。

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

大量載入最佳做法

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

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

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

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

• 使用 子句中的 OPENROWSET(BULK...) ORDER 和 UNIQUE 提示，指定源數據檔的排序方式。

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

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

測量和診斷 MERGE 效能

下列功能可協助您衡量及診斷 MERGE 陳述式的效能。

• 在 sys.dm_exec_query_optimizer_info 動態管理檢視表中使用 merge stmt 計數器可傳回適用於 MERGE 陳述式的查詢最佳化數目。
• 在 sys.dm_exec_plan_attributes 動態管理檢視表中使用 merge_action_type 屬性可傳回當作 MERGE 陳述式結果使用的觸發程序執行計劃類型。
• 請使用延伸事件工作階段來蒐集 MERGE 陳述式的疑難排解資料，就像是處理其他資料操作語言 (DML) 陳述式一樣。 如需擴充事件概觀的詳細資訊，請參閱快速入門：擴充事件和使用 SSMS XEvent 分析工具。

範例

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

如果存在相符的資料列，則常見情況是更新資料表中的一或多個資料行。 另一個情況是，如果沒有相符的資料列，則將資料作為新資料列插入。 您通常會透過將參數傳遞到包含適當 UPDATE 和 INSERT 陳述式的預存程序，依照上述情況之一來執行。 您可以使用 MERGE 陳述式，在單一陳述式中同時執行兩個工作。 下列範例顯示 AdventureWorks2022 資料庫中包含 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 根據數據表中處理的順序，每天更新 AdventureWorks2022 範例資料庫中的 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 AdventureWorks2022 資料庫中的數據表。

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

OUTPUT 子句很適合用來查詢 MERGE 陳述式的結果，如需詳細資訊，請參閱 OUTPUT 子句。 此範例也示範如何將 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 語句的結果插入另一個數據表

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

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 邊緣資料表。 您將在 livesIn 邊緣使用 MERGE 陳述式，以在 Person 與 City 之間還未存在邊緣時插入新資料列。 如果邊緣已經存在，則您只需更新 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

相關內容

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