SQLServer中MERGE語句的使用

更新時(shí)間：2024年08月05日 11:31:44 作者：zxrhhm

MERGE語句用于根據(jù)兩個(gè)表之間的條件來插入、更新或刪除記錄,本文主要介紹了SQLServer中MERGE語句的使用,具有一定的參考價(jià)值,感興趣的可以了解一下

9.1、借助派生的源表，使用 MERGE 對目標(biāo)表執(zhí)行 UPDATE 和 INSERT 操作
9.2、使用 MERGE 在一個(gè)語句中對表執(zhí)行 INSERT 和 UPDATE 操作
9.3、使用 MERGE 在一個(gè)語句中對表執(zhí)行 UPDATE 和 DELETE 操作
9.4、將 MERGE 語句的執(zhí)行結(jié)果插入到另一個(gè)表中
9.5、使用 MERGE 對圖形數(shù)據(jù)庫中的目標(biāo)邊緣表執(zhí)行 INSERT 或 UPDATE 操作

10、相關(guān)內(nèi)容

在 SQL Server 中，MERGE 語句用于根據(jù)兩個(gè)表之間的條件來插入、更新或刪除記錄。它通常用于同步兩個(gè)表的數(shù)據(jù)，其中一個(gè)表是源表（包含要插入或更新的數(shù)據(jù)），另一個(gè)是目標(biāo)表（數(shù)據(jù)要插入或更新的表）。

1、本文內(nèi)容

語法
參數(shù)
備注
觸發(fā)器的實(shí)現(xiàn)
權(quán)限
有關(guān)索引的最佳做法
MERGE 的并發(fā)注意事項(xiàng)
示例
相關(guān)內(nèi)容

適用于：

SQL Server
Azure SQL 數(shù)據(jù)庫
Azure SQL 托管實(shí)例
Azure Synapse Analytics

根據(jù)與源表聯(lián)接的結(jié)果，對目標(biāo)表進(jìn)行插入、更新或刪除操作。例如，根據(jù)與另一個(gè)表的區(qū)別，在一個(gè)表中插入、更新或刪除行，從而同步兩個(gè)表。

2、語法

SQL Server 和 Azure SQL 數(shù)據(jù)庫的語法：

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

3、參數(shù)

WITH common_table_expression<>
指定在 MERGE 語句作用域內(nèi)定義的臨時(shí)命名結(jié)果集或視圖，亦稱為“公用表表達(dá)式”。結(jié)果集派生自簡單查詢，并由 MERGE 語句引用。有關(guān)詳細(xì)信息，請參閱 WITH common_table_expression (Transact-SQL)。
TOP ( expression ) [ PERCENT ]
指定受影響的行數(shù)或所占百分比。 expression 可以是行數(shù)或行百分比。在 TOP 表達(dá)式中引用的行不是以任意順序排列的。有關(guān)詳細(xì)信息，請參閱 TOP (Transact-SQL)。

在整個(gè)源表和目標(biāo)表聯(lián)接，且不符合插入、更新或刪除操作條件的聯(lián)接行遭刪除后，應(yīng)用 TOP 子句。 TOP 子句進(jìn)一步將聯(lián)接行數(shù)減少到指定值。這些操作（插入、更新或刪除）以無序方式應(yīng)用于其余聯(lián)接行。也就是說，在 WHEN 子句中定義的操作中，這些行是無序分布的。例如，指定 TOP (10) 會(huì)影響 10 行。在這些行中，可能會(huì)更新 7 行并插入 3 行，也可能會(huì)刪除 1 行、更新 5 行并插入 4 行等。

如果源表上沒有篩選器， MERGE 語句可能會(huì)對源表執(zhí)行表掃描或聚集索引掃描，以及對目標(biāo)表進(jìn)行表掃描或聚集索引掃描。因此，即使使用 TOP 子句通過創(chuàng)建多個(gè)批處理來修改大型表，I/O 性能有時(shí)也會(huì)受到影響。在這種情況下，請務(wù)必要確保所有連續(xù)批處理都以新行為目標(biāo)。
database_name
target_table 所在數(shù)據(jù)庫的名稱。
schema_name
target_table 所屬架構(gòu)的名稱。
target_table
<table_source> 中的數(shù)據(jù)行根據(jù) <clause_search_condition> 進(jìn)行匹配的表或視圖。 target_table 是由 MERGE 語句的 WHEN 子句指定的任何插入、更新或刪除操作的目標(biāo)。

如果 target_table 為視圖，則針對它的任何操作都必須滿足更新視圖所需的條件。有關(guān)詳細(xì)信息，請參閱通過視圖修改數(shù)據(jù)。

target_table 不得是遠(yuǎn)程表。 target_table 不能定義其中的任何規(guī)則。target_table 不能是內(nèi)存優(yōu)化表。

可以將提示指定為 <merge_hint>。
[ AS ] table_alias
用于為 target_table 引用表的替代名稱。
USING <table_source>
指定根據(jù) <merge_search_condition> 與 target_table 中的數(shù)據(jù)行進(jìn)行匹配的數(shù)據(jù)源。此匹配的結(jié)果指出了要由 MERGE 語句的 WHEN 子句采取的操作。 <table_source> 可以是一個(gè)遠(yuǎn)程表，或者是一個(gè)能夠訪問遠(yuǎn)程表的派生表。

<table_source> 可以是一個(gè)派生表，它使用 Transact-SQL 表值構(gòu)造函數(shù)通過指定多行來構(gòu)造表。
[ AS ] table_alias
用于為 table_source 引用表的替代名稱。

有關(guān)此子句的語法和參數(shù)的詳細(xì)信息，請參閱 FROM (Transact-SQL)。
ON <merge_search_condition>
指定聯(lián)接 <table_source> 與 target_table 以確定匹配位置所要滿足的條件。

注意

請務(wù)必僅指定目標(biāo)表中用于匹配目的的列。也就是說，指定與源表中的對應(yīng)列進(jìn)行比較的目標(biāo)表列。請勿嘗試通過在 ON 子句中篩選掉目標(biāo)表中的行（如指定 AND NOT target_table.column_x = value）來提高查詢性能。這樣做可能會(huì)返回意外和不正確的結(jié)果。
WHEN MATCHED THEN <merge_matched>
指定根據(jù) <merge_matched> 子句更新或刪除 *target_table 中所有與 <table_source> ON <merge_search_condition> 返回的行匹配、且滿足其他所有搜索條件的行。

MERGE 語句最多可以有兩個(gè) WHEN MATCHED 子句。如果指定了兩個(gè)子句，第一個(gè)子句必須隨附 AND <search_condition> 子句。對于任何給定行，只有在未應(yīng)用第一個(gè) WHEN MATCHED 子句時(shí)，才會(huì)應(yīng)用第二個(gè) WHEN MATCHED 子句。如果有兩個(gè) WHEN MATCHED 子句，一個(gè)必須指定 UPDATE 操作，另一個(gè)必須指定 DELETE 操作。如果在 <merge_matched> 子句中指定了 UPDATE，并且根據(jù) <merge_search_condition><table_source> 中有多行與 target_table 中的一行匹配， SQL Server 便會(huì)返回錯(cuò)誤。 MERGE 語句無法多次更新同一行，也無法更新和刪除同一行。
WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
指定針對 <table_source> ON <merge_search_condition> 返回且不與 target_table 中的行匹配、但滿足其他搜索條件（若有）的所有行，將一行插入 target_table 中。要插入的值是由 <merge_not_matched> 子句指定的。 MERGE 語句只能有一個(gè) WHEN NOT MATCHED [ BY TARGET ] 子句。
WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
指定根據(jù) <merge_matched> 子句更新或刪除 *target_table 中所有與 <table_source> ON <merge_search_condition> 返回的行不匹配而滿足其他所有搜索條件的行。

MERGE 語句最多可以有兩個(gè) WHEN NOT MATCHED BY SOURCE 子句。如果指定了兩個(gè)子句，第一個(gè)子句必須隨附 AND <clause_search_condition> 子句。對于任何給定行，只有在未應(yīng)用第一個(gè) WHEN NOT MATCHED BY SOURCE 子句時(shí)，才會(huì)應(yīng)用第二個(gè) WHEN NOT MATCHED BY SOURC 子句。如果有兩個(gè) WHEN NOT MATCHED BY SOURCE 子句，那么其中的一個(gè)必須指定 UPDATE 操作，而另一個(gè)必須指定 DELETE 操作。在 <clause_search_condition> 中只能引用目標(biāo)表中的列。

如果 <table_source> 未返回任何行，無法訪問源表中的列。如果 <merge_matched> 子句中指定的更新或刪除操作引用了源表中的列，則會(huì)返回錯(cuò)誤 207（列名無效）。例如，由于無法訪問源表中的 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1，因此 Col1 子句可能導(dǎo)致該語句失敗。
AND <clause_search_condition>
指定任何有效的搜索條件。有關(guān)詳細(xì)信息，請參閱搜索條件 (Transact-SQL)。
<table_hint_limited>
指定針對 MERGE 語句完成的每個(gè)插入、更新或刪除操作，對目標(biāo)表應(yīng)用的一個(gè)或多個(gè)表提示。需要有 WITH 關(guān)鍵字和括號。

禁止使用 NOLOCK 和 READUNCOMMITTED。有關(guān)表提示的詳細(xì)信息，請參閱表提示 (Transact-SQL)。

對作為 INSERT 語句目標(biāo)的表指定 TABLOCK 提示，與指定 TABLOCKX 提示的效果相同。對表采用排他鎖。如果指定了 FORCESEEK，它會(huì)應(yīng)用于與源表聯(lián)接的目標(biāo)表的隱式實(shí)例。

注意

指定帶有 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 的 READPAST 可能會(huì)導(dǎo)致違反 UNIQUE 約束的 INSERT 操作。
INDEX ( index_val [，…n ] )
指定目標(biāo)表上一個(gè)或多個(gè)索引的名稱或 ID，以執(zhí)行與源表的隱式聯(lián)接。有關(guān)詳細(xì)信息，請參閱表提示 (Transact-SQL)。
<output_clause>
針對 target_table 中不按照任何特定順序更新、插入或刪除的所有行返回一行。 $action 可在 output 子句中指定。 $action 是類型為 nvarchar(10) 的列，它返回每行（INSERT，UPDATE 或 DELETE）3 個(gè)值中的 1 個(gè)（具體視對相應(yīng)行完成的操作而定）。建議使用 OUTPUT 子句來查詢或計(jì)算受 MERGE 影響的行。有關(guān)該子句的參數(shù)和行為的詳細(xì)信息，請參閱 OUTPUT 子句 (Transact-SQL)。
OPTION ( <query_hint> [ ,…n ] )
指定優(yōu)化器提示用于自定義數(shù)據(jù)庫引擎處理語句的方式。有關(guān)詳細(xì)信息，請參閱提示 (Transact-SQL) - 查詢。
<merge_matched>
指定更新或刪除操作，應(yīng)用于 target_table 中所有不與 <table_source> ON <merge_search_condition> 返回的行匹配、但滿足其他所有搜索條件的行。
UPDATE SET <set_clause>
指定目標(biāo)表中要更新的列或變量名稱的列表，以及用來更新它們的值。

有關(guān)該子句的參數(shù)的詳細(xì)信息，請參閱 UPDATE (Transact-SQL)。不支持將變量設(shè)置為與列相同的值。
DELETE
指定刪除與 target_table 中的行匹配的行。
<merge_not_matched>
指定要插入到目標(biāo)表中的值。
( column_list )
要在其中插入數(shù)據(jù)的目標(biāo)表中一個(gè)或多個(gè)列的列表。必須使用單一部分名稱來指定這些列，否則 MERGE 語句將失敗。必須用括號將 column_list 括起來，并且用逗號進(jìn)行分隔。
VALUES ( values_list)
返回要插入到目標(biāo)表中的值的常量、變量或表達(dá)式的逗號分隔列表。表達(dá)式不得包含 EXECUTE 語句。
DEFAULT VALUES
強(qiáng)制插入的行包含為每個(gè)列定義的默認(rèn)值。

有關(guān)此子句的詳細(xì)信息，請參閱 INSERT (Transact-SQL)。
<search_condition>
指定用于指定 <merge_search_condition> 或 <clause_search_condition> 的搜索條件。有關(guān)此子句的參數(shù)的詳細(xì)信息，請參閱搜索條件 (Transact-SQL)。
graph search pattern
指定圖匹配模式。有關(guān)此子句參數(shù)的詳細(xì)信息，請參閱 MATCH (Transact-SQL)。

4、備注

必須指定三個(gè) MATCHED 子句中的至少一個(gè)子句，但可以按任何順序指定。無法在同一個(gè) MATCHED 子句中多次更新一個(gè)變量。

MERGE 語句對目標(biāo)表指定的任何插入、更新或刪除操作受限于，在此語句中定義的任何約束，包括任何級聯(lián)引用完整性約束。如果 IGNORE_DUP_KEY 對目標(biāo)表中的任何唯一索引都設(shè)置為 ON，MERGE 便會(huì)忽略此設(shè)置。

MERGE 語句需要一個(gè)分號 (?? 作為語句終止符。如果運(yùn)行沒有終止符的 MERGE 語句，將引發(fā)錯(cuò)誤 10713。

如果在 MERGE 之后使用，@@ROWCOUNT (Transact-SQL) 會(huì)返回為客戶端插入、更新和刪除的行的總數(shù)。

在數(shù)據(jù)庫兼容級別設(shè)置為 100 或更高時(shí)，MERGE 為完全保留的關(guān)鍵字。 MERGE 語句可用于設(shè)置為 90 和 100 的數(shù)據(jù)庫兼容性級別；不過，當(dāng)數(shù)據(jù)庫兼容性級別設(shè)置為 90 時(shí)，關(guān)鍵字不是完全保留的。

5、觸發(fā)器的實(shí)現(xiàn)

對于在 MERGE 語句中指定的每個(gè)插入、更新或刪除操作，SQL Server 都會(huì)觸發(fā)對目標(biāo)表定義的任何對應(yīng) AFTER 觸發(fā)器，但不保證哪個(gè)操作最先或最后觸發(fā)觸發(fā)器。為相同操作定義的觸發(fā)器會(huì)遵循您指定的順序進(jìn)行觸發(fā)。有關(guān)設(shè)置觸發(fā)器激發(fā)順序的詳細(xì)信息，請參閱指定第一個(gè)和最后一個(gè)觸發(fā)器。

如果目標(biāo)表已針對 MERGE 語句完成的插入、更新或刪除操作啟用了對自己定義的 INSTEAD OF 觸發(fā)器，它必須已針對 MERGE 語句中指定的所有操作啟用了 INSTEAD OF 觸發(fā)器。

如果對 target_table 定義了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 觸發(fā)器，則不會(huì)運(yùn)行更新或刪除操作。而是會(huì)觸發(fā)觸發(fā)器，并相應(yīng)地填充 inserted 和 deleted 表。

如果對 target_table 定義了任何 INSTEAD OF INSERT 觸發(fā)器，則不會(huì)執(zhí)行插入操作。而是相應(yīng)地填充表。

備注與單獨(dú)的 INSERT 、 UPDATE 和 DELETE 語句不同，觸發(fā)器內(nèi)部由 @@ROWCOUNT 反映的行數(shù)可能更高。任何 AFTER 觸發(fā)器（無論觸發(fā)器捕獲的數(shù)據(jù)修改語句如何）內(nèi)的 @@ROWCOUNT 都將反映受 MERGE 影響的總行數(shù)。例如，如果 MERGE 語句插入一行、更新一行并刪除一行，則任何 AFTER 觸發(fā)器的 @@ROWCOUNT 都為 3，即使觸發(fā)器僅為 INSERT 語句聲明。

6、權(quán)限

需要對源表的 SELECT 權(quán)限和對目標(biāo)表的 INSERT、UPDATE 或 DELETE 權(quán)限。有關(guān)詳細(xì)信息，請參閱 SELECT、INSERT、UPDATE 和 DELETE 文章中的“權(quán)限”部分。

7、有關(guān)索引的最佳做法

通過使用 MERGE 語句，可以使用單個(gè)語句替換各個(gè) DML 語句。由于操作是在單個(gè)語句中執(zhí)行的，因此可以提高查詢性能，從而最大限度地減少處理源表和目標(biāo)表中數(shù)據(jù)的次數(shù)。然而，性能的提升取決于是否進(jìn)行了正確的索引和聯(lián)接以及是否遵守了其他注意事項(xiàng)。

若要提高 MERGE 語句的性能，我們建議您遵循以下索引準(zhǔn)則：

創(chuàng)建索引以促進(jìn) MERGE 的源與目標(biāo)之間的聯(lián)接：
- 在源表的聯(lián)接列上創(chuàng)建索引，該索引具有涵蓋目標(biāo)表的聯(lián)接邏輯的鍵。如果可能，該索引應(yīng)該是唯一的。
- 此外，在目標(biāo)表中的聯(lián)接列上創(chuàng)建索引。如果可能，該索引應(yīng)該是唯一的聚集索引。
- 這兩個(gè)索引可確保對表中的數(shù)據(jù)進(jìn)行排序，而唯一性有助于進(jìn)行比較。因?yàn)椴樵儍?yōu)化器不需要執(zhí)行額外驗(yàn)證處理即可定位和更新重復(fù)的行，也不需要執(zhí)行其他排序操作，所以查詢性能得到了提高。
避免將具有任何形式的列存儲(chǔ)索引的表作為 MERGE 語句的目標(biāo)。與任何 UPDATE 一樣，通過更新暫存行存儲(chǔ)表，然后執(zhí)行批量 DELETE 和 INSERT（而不是 UPDATE 或 MERGE）操作，你可能會(huì)發(fā)現(xiàn)列存儲(chǔ)索引的性能更好。

8、MERGE 的并發(fā)注意事項(xiàng)

在鎖定方面，MERGE 不同于離散的、連續(xù) INSERT、UPDATE 和 DELETE 語句。 MERGE 仍執(zhí)行 INSERT、UPDATE 和 DELETE 操作，但使用的是不同的鎖定機(jī)制。為滿足某些應(yīng)用程序的需要，編寫離散的 INSERT、UPDATE 和 DELETE 語句可能更高效。 MERGE 可能會(huì)大規(guī)模引入復(fù)雜的并發(fā)問題或需要高級故障排除。因此，計(jì)劃在部署到生產(chǎn)環(huán)境之前全面測試任何 MERGE 語句。

MERGE 語句非常適合在以下（但不限于）場景中替代離散 INSERT、UPDATE 和 DELETE 操作：

涉及大量行計(jì)數(shù)的 ETL 操作在不需要其他并發(fā)操作的時(shí)間段內(nèi)執(zhí)行。當(dāng)需要大量并發(fā)時(shí)，單獨(dú)的 INSERT、UPDATE 和 DELETE 邏輯性能可能比 MERGE 語句更佳，其阻塞性問題更少。
涉及較小行計(jì)數(shù)的復(fù)雜操作和不太可能長時(shí)間執(zhí)行的事務(wù)。
涉及用戶表的復(fù)雜操作，其中索引可設(shè)計(jì)為確保最佳執(zhí)行計(jì)劃，避免表掃描和查找以支持索引掃描或索引查找（理想情況）。

并發(fā)的其他注意事項(xiàng)：

在某些希望通過 MERGE 插入和更新唯一鍵的情況下，指定 HOLDLOCK 將防止出現(xiàn)唯一鍵沖突。 HOLDLOCK 是 SERIALIZABLE 事務(wù)隔離級別的同義詞，它不支持其他并發(fā)事務(wù)修改該事務(wù)已讀取的數(shù)據(jù)。 SERIALIZABLE 是安全性最高的隔離級別，但提供與其他事務(wù)最低的并發(fā)性，保留對數(shù)據(jù)范圍的鎖定，以防止在讀取過程中插入或更新虛擬行。有關(guān) HOLDLOCK 的詳細(xì)信息，請參閱提示和 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

測量和診斷 MERGE 性能以下功能可幫助您測量和診斷 MERGE 語句的性能。

在 sys.dm_exec_query_optimizer_info 動(dòng)態(tài)管理視圖中使用 merge stmt 計(jì)數(shù)器，以返回用于 MERGE 語句的查詢優(yōu)化數(shù)。
在 sys.dm_exec_plan_attributes 動(dòng)態(tài)管理視圖中使用 merge_action_type 屬性，以返回用作 MERGE 語句結(jié)果的觸發(fā)器執(zhí)行計(jì)劃的類型。
使用擴(kuò)展事件會(huì)話收集 MERGE 語句的故障排除數(shù)據(jù)，其方式與用于其他數(shù)據(jù)操作語言 (DML) 語句的方式相同。有關(guān)擴(kuò)展事件概述的詳細(xì)信息，請參閱快速入門：擴(kuò)展事件和使用 SSMS XEvent 探查器。

9、示例

9.1、借助派生的源表，使用 MERGE 對目標(biāo)表執(zhí)行 UPDATE 和 INSERT 操作

下面的示例使用 MERGE 以更新或插入行的方式來修改數(shù)據(jù)庫中的 T_UnitMeasure表。

CREATE TABLE T_UnitMeasure(UnitMeasureCode  NCHAR(8),NameInfo NVARCHAR(25));
INSERT INTO T_UnitMeasure VALUES(N'Car00001',N'問界M5'),(N'Car00004',N'問界M9');
SELECT * FROM T_UnitMeasure;

在這里插入圖片描述

當(dāng)源表中的 NewCode 值與目標(biāo)表T_UnitMeasure 的(UnitMeasureCode ) 列中的值匹配時(shí)，就會(huì)更新此目標(biāo)表中的 NameInfo 列。如果 NewCode 的值不匹配，就會(huì)將源行插入目標(biāo)表中。此源表是一個(gè)派生表，它使用 Transact-SQL 表值構(gòu)造函數(shù)指定源表的多個(gè)行。有關(guān)在派生表中使用表值構(gòu)造函數(shù)的詳細(xì)信息，請參閱表值構(gòu)造函數(shù) (Transact-SQL)。

OUTPUT 子句可用于查詢 MERGE 語句的結(jié)果，有關(guān)詳細(xì)信息，請參閱 OUTPUT 子句。下面的示例還展示了如何在表變量中存儲(chǔ) OUTPUT 子句的結(jié)果。然后，通過運(yùn)行返回已插入行數(shù)和已更新行數(shù)的簡單選擇操作，匯總 MERGE 語句的結(jié)果。

DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));  
  
MERGE INTO T_UnitMeasure AS Target  
USING (VALUES (N'Car00001',N'問界M9'), (N'Car00002',N'坦克700'), (N'Car00003',N'奔馳大G500'))  
       AS Source (NewCode, NewNameInfo)  
ON Target.UnitMeasureCode = Source.NewCode  
WHEN MATCHED THEN  
  UPDATE SET NameInfo = Source.NewNameInfo  
WHEN NOT MATCHED BY TARGET THEN  
  INSERT (UnitMeasureCode, NameInfo) VALUES (NewCode, NewNameInfo)  
OUTPUT $action INTO @SummaryOfChanges;  
  
-- Query the results of the table variable.  
SELECT Change, COUNT(*) AS CountPerChange  
FROM @SummaryOfChanges  
GROUP BY Change;

在這里插入圖片描述

查詢目標(biāo)表T_UnitMeasure返回的結(jié)果復(fù)合預(yù)期，Car00001=問界M5更新為“問界M9”，其他進(jìn)行了插入操作。

9.2、使用 MERGE 在一個(gè)語句中對表執(zhí)行 INSERT 和 UPDATE 操作

常見方案是，更新表中的一個(gè)或多個(gè)列（若有匹配行）。或者，在沒有匹配行的情況下，將數(shù)據(jù)作為新行插入。處理兩種方案之一的一般方法是，將參數(shù)傳遞給包含相應(yīng) UPDATE 和 INSERT 語句的存儲(chǔ)過程。借助 MERGE 語句，可以在一個(gè)語句中同時(shí)執(zhí)行這兩項(xiàng)任務(wù)。下面的示例顯示了數(shù)據(jù)庫中一個(gè)同時(shí)包含 INSERT 語句和 UPDATE 語句的存儲(chǔ)過程。隨后，過程被修改為，使用一個(gè) MERGE 語句運(yùn)行等效的操作。

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

    -- Update the row if it exists.
    UPDATE T_UnitMeasure
    SET NameInfo = @NameInfo
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO T_UnitMeasure (UnitMeasureCode,NameInfo) VALUES (@UnitMeasureCode, @NameInfo)
    END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'Car00003', @NameInfo = N'奔馳E300L';

SELECT * FROM T_UnitMeasure;

UnitMeasureCode NameInfo
--------------- -------------------------
Car00001        問界M9
Car00004        問界M9
Car00002        坦克700
Car00003        奔馳E300L

(4 行受影響)

-- 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(8),
    ExistingName NVARCHAR(50),
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(8),
    NewName NVARCHAR(50),
);
GO

DROP TABLE #MyTempTable

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

    MERGE T_UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @NameInfo) AS src (UnitMeasureCode, NameInfo)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED THEN
      UPDATE SET NameInfo = src.NameInfo
    WHEN NOT MATCHED BY TARGET THEN
      INSERT (UnitMeasureCode,NameInfo) VALUES (@UnitMeasureCode, @NameInfo)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'Car00001', @NameInfo = N'問界M7';
EXEC InsertUnitMeasure @UnitMeasureCode = 'Car00002', @NameInfo = N'豐田皇冠';
EXEC InsertUnitMeasure @UnitMeasureCode = 'Car00005', @NameInfo = N'寶馬X5';


SELECT * FROM #MyTempTable;

ExistingCode ExistingName       ActionTaken NewCode        NewName
------------ ------------------ --------    -----------  ---------- 
Car00001     問界M9               UPDATE      Car00001 		問界M7
Car00002     坦克700              UPDATE      Car00002 		豐田皇冠
NULL         NULL                INSERT      Car00005 		寶馬X5
(3 行受影響)

以上操作完成執(zhí)行成功后，查詢結(jié)果如下：

SELECT * FROM T_UnitMeasure;

UnitMeasureCode NameInfo
--------------- -------------------------
Car00001        問界M7
Car00004        問界M9
Car00002        豐田皇冠
Car00003        奔馳E300L
Car00005        寶馬X5

(5 行受影響)

9.3、使用 MERGE 在一個(gè)語句中對表執(zhí)行 UPDATE 和 DELETE 操作

下面的示例使用 MERGE 根據(jù) T_UnitMeasure 表中記錄。通過merge 更新或刪除 T_UnitMeasure 表數(shù)據(jù)。

SELECT * FROM T_UnitMeasure

UnitMeasureCode NameInfo
--------------- -------------------------
Car00001        問界M7
Car00004        問界M9
Car00002        豐田皇冠
Car00003        奔馳E300L
Car00005        寶馬X5

(5 行受影響)
-- 更新及刪除
MERGE T_UnitMeasure AS Target
USING (VALUES (N'Car00001',N'問界M5'), (N'Car00004',N'問界M9'))  
       AS Source (NewCode, NewNameInfo)  
ON Target.UnitMeasureCode = Source.NewCode  
WHEN MATCHED AND Target.NameInfo=Source.NewNameInfo THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET NameInfo = Source.NewNameInfo
OUTPUT $action,
    Inserted.UnitMeasureCode,
    Inserted.NameInfo,
    Deleted.UnitMeasureCode,
    Deleted.NameInfo;

$action    UnitMeasureCode NameInfo                  UnitMeasureCode NameInfo
---------- --------------- ------------------------- --------------- -------------------------
UPDATE     Car00001        問界M5                      Car00001        問界M7
DELETE     NULL            NULL                      Car00004        問界M9

(2 行受影響)

SELECT * FROM T_UnitMeasure

UnitMeasureCode NameInfo
--------------- -------------------------
Car00001        問界M5
Car00002        豐田皇冠
Car00003        奔馳E300L
Car00005        寶馬X5

(4 行受影響)

9.4、將 MERGE 語句的執(zhí)行結(jié)果插入到另一個(gè)表中

CREATE TABLE T_UnitMeasure_list 
( ID BIGINT IDENTITY(1,1) PRIMARY KEY, 
  UnitMeasureCode  NCHAR(8),
  NameInfo NVARCHAR(25),
  Action NVARCHAR(16),
  UnitMeasureCode_old NCHAR(8),
  NameInfo_old  NVARCHAR(25),
);
GO

INSERT INTO T_UnitMeasure_list
SELECT UnitMeasureCode,NameInfo,Action,UnitMeasureCode_old,NameInfo_old
FROM (
    MERGE T_UnitMeasure AS pi
    USING (VALUES (N'Car00005',N'寶馬X5'), (N'Car00001',N'問界M9'))  AS src (NewCode, NewNameInfo) 
        ON pi.UnitMeasureCode = src.NewCode
    WHEN MATCHED AND pi.NameInfo=src.NewNameInfo THEN 
      DELETE
	WHEN MATCHED THEN
	  UPDATE SET pi.NameInfo = src.NewNameInfo
    OUTPUT $action,
        Inserted.UnitMeasureCode,
        Inserted.NameInfo,
        Deleted.UnitMeasureCode AS UnitMeasureCode_old,
        Deleted.NameInfo AS NameInfo_old
    ) AS Changes(Action, UnitMeasureCode,NameInfo, UnitMeasureCode_old, NameInfo_old)
;
GO

SELECT * FROM T_UnitMeasure_list;

ID                   UnitMeasureCode NameInfo                  Action           UnitMeasureCode_old NameInfo_old
-------------------- --------------- ------------------------- ---------------- ------------------- -------------------------
1                    Car00001        問界M9                      UPDATE           Car00001            問界M5
2                    NULL            NULL                        DELETE           Car00005            寶馬X5

(2 行受影響)

SELECT * FROM T_UnitMeasure;

UnitMeasureCode NameInfo
--------------- -------------------------
Car00001        問界M9
Car00002        豐田皇冠
Car00003        奔馳E300L

(3 行受影響)

9.5、使用 MERGE 對圖形數(shù)據(jù)庫中的目標(biāo)邊緣表執(zhí)行 INSERT 或 UPDATE 操作

在此示例中，創(chuàng)建節(jié)點(diǎn)表 Person 和 City 以及邊緣表 livesIn。如果 Person 和 City 之間尚不存在 livesIn 邊緣，則對邊緣使用 MERGE 語句，并插入新行。如果已有邊緣，只需更新 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

在這里插入圖片描述

10、相關(guān)內(nèi)容

SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)
OUTPUT 子句 (Transact-SQL)
在 Integration Services 包中執(zhí)行 MERGE
FROM (Transact-SQL)
表值構(gòu)造函數(shù) (Transact-SQL)

到此這篇關(guān)于SQLServer中MERGE語句的使用的文章就介紹到這了,更多相關(guān)SQL MERGE語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

SQL
MERGE

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQLServer中MERGE語句的使用

目錄

1、本文內(nèi)容

2、語法

3、參數(shù)

4、備注

5、觸發(fā)器的實(shí)現(xiàn)

6、權(quán)限

7、有關(guān)索引的最佳做法

8、MERGE 的并發(fā)注意事項(xiàng)

9、示例

9.1、借助派生的源表，使用 MERGE 對目標(biāo)表執(zhí)行 UPDATE 和 INSERT 操作

9.2、使用 MERGE 在一個(gè)語句中對表執(zhí)行 INSERT 和 UPDATE 操作

9.3、使用 MERGE 在一個(gè)語句中對表執(zhí)行 UPDATE 和 DELETE 操作

9.4、將 MERGE 語句的執(zhí)行結(jié)果插入到另一個(gè)表中

9.5、使用 MERGE 對圖形數(shù)據(jù)庫中的目標(biāo)邊緣表執(zhí)行 INSERT 或 UPDATE 操作

10、相關(guān)內(nèi)容

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQLServer中MERGE語句的使用

目錄

1、本文內(nèi)容

2、語法

3、參數(shù)

4、備注

5、觸發(fā)器的實(shí)現(xiàn)

6、權(quán)限

7、有關(guān)索引的最佳做法

8、MERGE 的并發(fā)注意事項(xiàng)

9、示例

9.1、借助派生的源表，使用 MERGE 對目標(biāo)表執(zhí)行 UPDATE 和 INSERT 操作

9.2、使用 MERGE 在一個(gè)語句中對表執(zhí)行 INSERT 和 UPDATE 操作

9.3、使用 MERGE 在一個(gè)語句中對表執(zhí)行 UPDATE 和 DELETE 操作

9.4、將 MERGE 語句的執(zhí)行結(jié)果插入到另一個(gè)表中

9.5、使用 MERGE 對圖形數(shù)據(jù)庫中的目標(biāo)邊緣表執(zhí)行 INSERT 或 UPDATE 操作

10、相關(guān)內(nèi)容

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

1、本文內(nèi)容

2、語法

3、參數(shù)

4、備注

5、觸發(fā)器的實(shí)現(xiàn)

6、權(quán)限

7、有關(guān)索引的最佳做法

8、MERGE 的并發(fā)注意事項(xiàng)

9、示例

9.1、借助派生的源表，使用 MERGE 對目標(biāo)表執(zhí)行 UPDATE 和 INSERT 操作

9.3、使用 MERGE 在一個(gè)語句中對表執(zhí)行 UPDATE 和 DELETE 操作

9.4、將 MERGE 語句的執(zhí)行結(jié)果插入到另一個(gè)表中

9.5、使用 MERGE 對圖形數(shù)據(jù)庫中的目標(biāo)邊緣表執(zhí)行 INSERT 或 UPDATE 操作

10、相關(guān)內(nèi)容