SQL Server 2008 MERGE语法

最新推荐文章于 2024-03-31 06:04:47 发布

cuanji3287

最新推荐文章于 2024-03-31 06:04:47 发布

阅读量252

点赞数

文章标签：数据库

根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。

语法

[ 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-515891/

cuanji3287

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 2008 MERGE语法

根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。语法 [ WITH [,...n] ]MERGE [ TOP...
复制链接

扫一扫