SQL Server 2008 MERGE語法

根據與源表聯接的結果，對目標表執行插入、更新或刪除操作。例如，根據在另一個表中找到的差異在一個表中插入、更新或刪除行，可以對兩個表進行同步。

 語法

[ WITH [,...n] ]
MERGE
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias ]
        USING
        ON
        [ WHEN MATCHED [ AND ]
            THEN ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
            THEN ]
        [ WHEN NOT MATCHED BY SOURCE [ AND ]
            THEN ]
        [ ]
        [ OPTION ( [ ,...n ] ) ]   
;

::=
{
    { [ [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

::=
{
        table_or_view_name [ [ AS ] table_alias ] [ ]
            [ WITH ( table_hint [ [ , ]...n ] ) ]
    | rowset_function [ [ AS ] table_alias ]
            [ ( bulk_column_alias [ ,...n ] ) ]
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
    |
    |
    |
}

::=
   

::=
    { UPDATE SET | DELETE }

::=
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 ]

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

::=
   

::=
    { [ NOT ] | ( ) }
    [ { AND | OR } [ NOT ] { | ( ) } ]
[ ,...n ]

::=
    { 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 ]
        [ OUTPUT INTO { @table_variable | output_table }
            [ (column_list) ] ]
}

::=
        { | scalar_expression }
                [ [AS] column_alias_identifier ] [ ,...n ]

::=
        { DELETED | INSERTED | from_table_name } . { * | column_name }
      | $action
 引數
WITH
指定在 MERGE 語句作用域內定義的臨時命名結果集或檢視，也稱為公用表表示式。該結果集派生自一個簡單查詢，並由 MERGE 語句引用。有關詳細資訊，請參閱 WITH common_table_expression (Transact-SQL)。

TOP ( expression ) [ PERCENT ]
指定受影響的行數或行百分比。expression 可以為行數，也可以為行百分比。在 TOP 表示式中引用的行不是以任意順序排列的。有關詳細資訊，請參閱 TOP (Transact-SQL)。

在聯接整個源表和整個目標表並且刪除了不符合插入、更新或刪除操作條件的聯接行之後，應用 TOP 子句。TOP 子句將聯接行的數量進一步減少為指定值，並且以一種無序方式對其餘聯接行應用插入、更新或刪除操作。也就是說，在 WHEN 子句中定義的操作中，這些行是無序分佈的。例如，如果指定 TOP (10)，將會影響 10 行；在這些行中，可能會更新 7 行而插入 3 行，或者可能刪除 1 行、更新 5 行並且插入 4 行，等等。

由於 MERGE 語句對源表和目標表都進行完全表掃描，因此在使用 TOP 子句通過建立多個批處理來修改大型表時，I/O 效能可能會受到影響。在這種情況下，一定要確保所有連續批處理都以新行作為處理目標。有關詳細資訊，請參閱優化 MERGE 語句效能。

target_table
表或檢視， 中的資料行將根據 與該表或檢視進行匹配。target_table 是由 MERGE 語句的 WHEN 子句指定的任何插入、更新或刪除操作的目標。

如果 target_table 為檢視，則針對它的任何操作都必須滿足更新檢視所需的條件。有關詳細資訊，請參閱通過檢視修改資料。target_table 不能是遠端表。target_table 不能具有針對它定義的任何規則。

[ AS ] table_alias
用於引用表的替代名稱。

USING
指定基於 與 target_table 中的資料行進行匹配的資料來源。此匹配的結果指出了要由 MERGE 語句的 WHEN 子句採取的操作。 可以是一個遠端表，或者是一個能夠訪問遠端表的派生表。

可以是一個使用 Transact-SQL 行建構函式功能在單個語句中指定多個行的派生表。

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

ON
指定在 與 target_table 進行聯接以確定它們的匹配位置時所遵循的條件。

注意：
重要的是，應僅指定目標表中用於匹配目的的列。也就是說，指定與源表中的對應列進行比較的目標表列。不要嘗試通過在 ON 子句中篩選掉目標表中的行來提高查詢效能，例如，指定 AND NOT target_table.column_x = value。這樣做可能會返回意外和不正確的結果。
 

WHEN MATCHED THEN
指定 target_table 中與 ON 返回的行匹配並滿足所有其他搜尋條件的所有行均應根據 子句進行更新或刪除。MERGE 語句最多可以有兩個 WHEN MATCHED 子句。如果指定了兩個子句，則第一個子句必須同時帶有一個 AND 子句。對於任何給定的行，只有在未應用第一個 WHEN MATCHED 子句的情況下，才會應用第二個 WHEN MATCHED 子句。如果有兩個 WHEN MATCHED 子句，那麼其中的一個必須指定 UPDATE 操作，而另一個必須指定 DELETE 操作。如果在 子句中指定了 UPDATE，並且根據 ， 中的多個行與 target_table 中的某一行匹配，則 SQL Server 將返回錯誤。MERGE 語句無法多次更新同一行，也無法更新和刪除同一行。

WHEN NOT MATCHED [ BY TARGET ] THEN
指定對於 ON 返回的每一行，如果該行與 target_table 中的行不匹配，但是滿足其他搜尋條件（如果存在），則在 target_table 中插入一行。要插入的值是由 子句指定的。MERGE 語句只能有一個 WHEN NOT MATCHED 子句。

WHEN NOT MATCHED BY SOURCE THEN
指定 target_table 中與 ON 返回的行不匹配但滿足所有其他搜尋條件的所有行均應根據 子句進行更新或刪除。

MERGE 語句最多可以有兩個 WHEN NOT MATCHED BY SOURCE 子句。如果指定了兩個子句，則第一個子句必須同時帶有一個 AND 子句。對於任何給定的行，只有當未應用第一個 WHEN NOT MATCHED BY SOURCE 子句時才會應用第二個子句。如果有兩個 WHEN NOT MATCHED BY SOURCE 子句，那麼其中的一個必須指定 UPDATE 操作，而另一個必須指定 DELETE 操作。在 中只能引用目標表中的列。

當 未返回任何行時，無法訪問源表中的列。如果 子句中指定的更新或刪除操作引用了源表中的列，將返回錯誤 207（無效列名）。例如，由於無法訪問源表中的 Col1，因此 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 子句可能導致該語句失敗。

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

為由 MERGE 語句執行的每個插入、更新或刪除操作指定對目標表應用的一個或多個表提示。需要有 WITH 關鍵字和括號。

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

對作為 INSERT 語句目標的表指定 TABLOCK 提示與指定 TABLOCKX 提示具有相同的效果。對錶採用排他鎖。如果指定了 FORCESEEK，會將其應用於與源表聯接的目標表的隱式例項。

注意：
指定帶有 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 的 READPAST 可能會導致違反 UNIQUE 約束的 INSERT 操作。
 

INDEX ( index_val [，...n ] )
指定目標表上的一個或多個索引的名稱或 ID，以執行與源表的隱式聯接。有關詳細資訊，請參閱表提示 (Transact-SQL)。

不按照任何特定順序為 target_table 中更新、插入或刪除的每一行返回一行。有關該子句的引數的詳細資訊，請參閱 OUTPUT 子句 (Transact-SQL)。

$action
在 OUTPUT 子句中指定一個 nvarchar(10) 型別的列，該子句為每一行返回以下三個值之一：'INSERT'、'UPDATE' 或 'DELETE'，具體返回其中哪個值取決於對該行執行的操作。

如果資料庫排序規則區分大小寫，則 $action 必須小寫。

OPTION ( [ ,...n ] )
指定使用優化器提示來自定義資料庫引擎處理語句的方式。有關詳細資訊，請參閱查詢提示 (Transact-SQL)。

指定更新或刪除操作，這些操作應用於 target_table 中與 ON 返回的行不匹配但滿足所有其他搜尋條件的所有行。

UPDATE SET
指定目標表中要更新的列或變數名的列表，以及用於更新它們的值。

有關該子句的引數的詳細資訊，請參閱 UPDATE (Transact-SQL)。不允許將變數設定為與列相同的值。

DELETE
指定刪除與 target_table 中的行匹配的行。

指定要插入到目標表中的值。

( column_list )
要在其中插入資料的目標表中的一列或多列的列表。必須使用單一部分名稱格式來指定這些列，否則 MERGE 語句將失敗。column_list 必須用圓括號括起來，並用逗號進行分隔。

VALUES ( values_list )
一個逗號分隔列表，其中包含常量、變數或者返回要插入到目標表中的值的表示式。表示式不能包含 EXECUTE 語句。

DEFAULT VALUES
強制插入的行包含為每個列定義的預設值。

有關該子句的詳細資訊，請參閱 INSERT (Transact-SQL)。

指定用於指定 或 的搜尋條件。有關該子句的引數的詳細資訊，請參閱搜尋條件 (Transact-SQL)。

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

由 MERGE 語句指定的目標表中的任何插入、更新或刪除操作都受為它定義的任何約束的限制，包括任何級聯引用完整性約束。如果 IGNORE_DUP_KEY 對於目標表中的任何唯一索引都設定為 ON，則 MERGE 將忽略此設定。

MERGE 語句需要一個分號 (;) 作為語句終止符。如果執行沒有終止符的 MERGE 語句，將引發錯誤 10713。

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

在資料庫相容級別設定為 100 的情況下，MERGE 是完全保留的關鍵字。MERGE 語句在資料庫相容級別為 90 和 100 的情況下也可用；但在資料庫相容級別設定為 90 時，此關鍵字不是完全保留的關鍵字。

觸發器的實現
對於在 MERGE 語句中指定的每個插入、更新或刪除操作，SQL Server 都會激發針對目標表定義的任何對應的 AFTER 觸發器，但不保證哪個操作最先或最後激發觸發器。為相同操作定義的觸發器會遵循您指定的順序進行觸發。有關設定觸發器激發順序的詳細資訊，請參閱指定第一個和最後一個觸發器。

對於由 MERGE 語句執行的插入、更新或刪除操作，如果目標表具有針對自己定義的已啟用 INSTEAD OF 觸發器，那麼對於在 MERGE 語句中指定的所有操作，它都必須具有已啟用的 INSTEAD OF 觸發器。

如果對 target_table 定義了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 觸發器，則不會執行更新或刪除操作，而是會激發觸發器並相應地填充 inserted 和 deleted 表。

如果對 target_table 定義了任何 INSTEAD OF INSERT 觸發器，則不會執行插入操作，而是會激發觸發器並相應地填充 inserted 表。

 許可權
需要對源表的 SELECT 許可權和對目標表的 INSERT、UPDATE 或 DELETE 許可權。有關其他資訊，請參閱 SELECT、INSERT、UPDATE 和 DELETE 主題中的“許可權”部分。

 示例
A. 使用 MERGE 在單個語句中對錶執行 UPDATE 和 DELETE 操作
下面的示例使用 MERGE 根據 SalesOrderDetail 表中已處理的訂單，每天更新 AdventureWorks 示例資料庫中的 ProductInventory 表。通過減去每天對 SalesOrderDetail 表中的每種產品所下的訂單數，更新 ProductInventory 表的 Quantity 列。如果某種產品的訂單數導致該產品的庫存量下降到 0 或更少，則會從 ProductInventory 表中刪除該產品對應的行。

 
USE AdventureWorks;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
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'
B. 藉助派生的源表，使用 MERGE 對目標表執行 UPDATE 和 INSERT 操作
下面的示例使用 MERGE 以更新或插入行的方式來修改 SalesReason 表。當源表中的 NewName 值與目標表 (SalesReason) 的 Name 列中的值匹配時，就會更新此目標表中的 ReasonType 列。當 NewName 的值不匹配時，就會將源行插入到目標表中。此源表是一個派生表，它使用 Transact-SQL 行建構函式功能指定源表的多個行。有關在派生表中使用行建構函式的詳細資訊，請參閱 FROM (Transact-SQL)。

 
USE AdventureWorks;
GO
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, inserted.*, deleted.*;
C. 將 MERGE 語句的執行結果插入到另一個表中
下例捕獲從 MERGE 語句的 OUTPUT 子句返回的資料，並將該資料插入另一個表。MERGE 語句根據在 SalesOrderDetail 表中處理的訂單，更新 ProductInventory 表的 Quantity 列。本示例捕獲已更新的行，並將這些行插入用於跟蹤庫存變化的另一個表中。

 
USE AdventureWorks;
GO
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

有關其他示例，請參閱使用 MERGE 插入、更新和刪除資料和優化 MERGE 語句效能。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515891/，如需轉載，請註明出處，否則將追究法律責任。