当前位置：首页 > 数据库 > 正文内容

SQLServer中MERGE语句的用法

2024-11-29数据库2

在 SQL Server 中，MERGE 语句用于根据两个表之间的条件来插入、更新或删除记录。它通常用于同步两个表的数据，其中一个表是源表（包含要插入或更新的数据），另一个是目标表（数据要插入或更新的表）。

1、本文内容

  • 语法

  • 参数

  • 备注

  • 触发器的实现

  • 权限

  • 有关索引的最佳做法

  • MERGE 的并发注意事项

  • 示例

  • 相关内容

适用于：

  • SQL Server

  • Azure SQL 数据库

  • Azure SQL 托管实例

  • Azure Synapse Analytics

根据与源表联接的结果，对目标表进行插入、更新或删除操作。 例如，根据与另一个表的区别，在一个表中插入、更新或删除行，从而同步两个表。

2、语法

SQL Server 和 Azure SQL 数据库的语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[ 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、参数

  • WITH common_table_expression<>
    指定在 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 性能有时也会受到影响。 在这种情况下，请务必要确保所有连续批处理都以新行为目标。

  • 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>
    指定根据 <merge_search_condition> 与 target_table 中的数据行进行匹配的数据源。 此匹配的结果指出了要由 MERGE 语句的 WHEN 子句采取的操作。 <table_source> 可以是一个远程表，或者是一个能够访问远程表的派生表。

    <table_source> 可以是一个派生表，它使用 Transact-SQL 表值构造函数通过指定多行来构造表。

  • [ AS ] table_alias
    用于为 table_source 引用表的替代名称。

    有关此子句的语法和参数的详细信息，请参阅 FROM (Transact-SQL)。

  • ON <merge_search_condition>
    指定联接 <table_source> 与 target_table 以确定匹配位置所要满足的条件。

    注意

    请务必仅指定目标表中用于匹配目的的列。 也就是说，指定与源表中的对应列进行比较的目标表列。 请勿尝试通过在 ON 子句中筛选掉目标表中的行（如指定 AND NOT target_table.column_x = value）来提高查询性能。 这样做可能会返回意外和不正确的结果。

  • WHEN MATCHED THEN <merge_matched>
    指定根据 <merge_matched> 子句更新或删除 *target_table 中所有与 <table_source> ON <merge_search_condition> 返回的行匹配、且满足其他所有搜索条件的行。

    MERGE 语句最多可以有两个 WHEN MATCHED 子句。 如果指定了两个子句，第一个子句必须随附 AND <search_condition> 子句。 对于任何给定行，只有在未应用第一个 WHEN MATCHED 子句时，才会应用第二个 WHEN MATCHED 子句。 如果有两个 WHEN MATCHED 子句，一个必须指定 UPDATE 操作，另一个必须指定 DELETE 操作。 如果在 <merge_matched> 子句中指定了 UPDATE，并且根据 <merge_search_condition><table_source> 中有多行与 target_table 中的一行匹配， SQL Server 便会返回错误。 MERGE 语句无法多次更新同一行，也无法更新和删除同一行。

  • WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
    指定针对 <table_source> ON <merge_search_condition> 返回且不与 target_table 中的行匹配、但满足其他搜索条件（若有）的所有行，将一行插入 target_table 中。 要插入的值是由 <merge_not_matched> 子句指定的。 MERGE 语句只能有一个 WHEN NOT MATCHED [ BY TARGET ] 子句。

  • WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
    指定根据 <merge_matched> 子句更新或删除 *target_table 中所有与 <table_source> ON <merge_search_condition> 返回的行不匹配而满足其他所有搜索条件的行。

    MERGE 语句最多可以有两个 WHEN NOT MATCHED BY SOURCE 子句。 如果指定了两个子句，第一个子句必须随附 AND <clause_search_condition> 子句。 对于任何给定行，只有在未应用第一个 WHEN NOT MATCHED BY SOURCE 子句时，才会应用第二个 WHEN NOT MATCHED BY SOURC 子句。 如果有两个 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 ] THEN INSERT 的 READPAST 可能会导致违反 UNIQUE 约束的 INSERT 操作。

  • INDEX ( index_val [，…n ] )
    指定目标表上一个或多个索引的名称或 ID，以执行与源表的隐式联接。 有关详细信息，请参阅表提示 (Transact-SQL)。

  • <output_clause>
    针对 target_table 中不按照任何特定顺序更新、插入或删除的所有行返回一行。 $action 可在 output 子句中指定。 $action 是类型为 nvarchar(10) 的列，它返回每行（INSERT，UPDATE 或 DELETE）3 个值中的 1 个（具体视对相应行完成的操作而定）。 建议使用 OUTPUT 子句来查询或计算受 MERGE 影响的行。 有关该子句的参数和行为的详细信息，请参阅 OUTPUT 子句 (Transact-SQL)。

  • OPTION ( <query_hint> [ ,…n ] )
    指定优化器提示用于自定义数据库引擎处理语句的方式。 有关详细信息，请参阅 提示 (Transact-SQL) - 查询。

  • <merge_matched>
    指定更新或删除操作，应用于 target_table 中所有不与 <table_source> ON <merge_search_condition> 返回的行匹配、但满足其他所有搜索条件的行。

  • UPDATE SET <set_clause>
    指定目标表中要更新的列或变量名称的列表，以及用来更新它们的值。

    有关该子句的参数的详细信息，请参阅 UPDATE (Transact-SQL)。 不支持将变量设置为与列相同的值。

  • DELETE
    指定删除与 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)。

  • graph search pattern
    指定图匹配模式。 有关此子句参数的详细信息，请参阅 MATCH (Transact-SQL)。

4、备注

必须指定三个 MATCHED 子句中的至少一个子句，但可以按任何顺序指定。 无法在同一个 MATCHED 子句中多次更新一个变量。

MERGE 语句对目标表指定的任何插入、更新或删除操作受限于，在此语句中定义的任何约束，包括任何级联引用完整性约束。 如果 IGNORE_DUP_KEY 对目标表中的任何唯一索引都设置为 ON，MERGE 便会忽略此设置。

MERGE 语句需要一个分号 (😉 作为语句终止符。 如果运行没有终止符的 MERGE 语句，将引发错误 10713。

如果在 MERGE 之后使用，@@ROWCOUNT (Transact-SQL) 会返回为客户端插入、更新和删除的行的总数。

在数据库兼容级别设置为 100 或更高时，MERGE 为完全保留的关键字。 MERGE 语句可用于设置为 90 和 100 的数据库兼容性级别；不过，当数据库兼容性级别设置为 90 时，关键字不是完全保留的。

5、触发器的实现

对于在 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 触发器，则不会执行插入操作。 而是相应地填充表。

备注与单独的 INSERT 、 UPDATE 和 DELETE 语句不同，触发器内部由 @@ROWCOUNT 反映的行数可能更高。 任何 AFTER 触发器（无论触发器捕获的数据修改语句如何）内的 @@ROWCOUNT 都将反映受 MERGE 影响的总行数。 例如，如果 MERGE 语句插入一行、更新一行并删除一行，则任何 AFTER 触发器的 @@ROWCOUNT 都为 3，即使触发器仅为 INSERT 语句声明。

6、权限

需要对源表的 SELECT 权限和对目标表的 INSERT、UPDATE 或 DELETE 权限。 有关详细信息，请参阅 SELECT、INSERT、UPDATE 和 DELETE 文章中的“权限”部分。

7、有关索引的最佳做法

通过使用 MERGE 语句，可以使用单个语句替换各个 DML 语句。 由于操作是在单个语句中执行的，因此可以提高查询性能，从而最大限度地减少处理源表和目标表中数据的次数。 然而，性能的提升取决于是否进行了正确的索引和联接以及是否遵守了其他注意事项。

若要提高 MERGE 语句的性能，我们建议您遵循以下索引准则：

  • 创建索引以促进 MERGE 的源与目标之间的联接：

    • 在源表的联接列上创建索引，该索引具有涵盖目标表的联接逻辑的键。 如果可能，该索引应该是唯一的。

    • 此外，在目标表中的联接列上创建索引。 如果可能，该索引应该是唯一的聚集索引。

    • 这两个索引可确保对表中的数据进行排序，而唯一性有助于进行比较。 因为查询优化器不需要执行额外验证处理即可定位和更新重复的行，也不需要执行其他排序操作，所以查询性能得到了提高。

  • 避免将具有任何形式的列存储索引的表作为 MERGE 语句的目标。 与任何 UPDATE 一样，通过更新暂存行存储表，然后执行批量 DELETE 和 INSERT（而不是 UPDATE 或 MERGE）操作，你可能会发现列存储索引的性能更好。

8、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)。

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 动态管理视图中使用 merge stmt 计数器，以返回用于 MERGE 语句的查询优化数。

  • 在 sys.dm_exec_plan_attributes 动态管理视图中使用 merge_action_type 属性，以返回用作 MERGE 语句结果的触发器执行计划的类型。

  • 使用扩展事件会话收集 MERGE 语句的故障排除数据，其方式与用于其他数据操作语言 (DML) 语句的方式相同。 有关扩展事件概述的详细信息，请参阅快速入门：扩展事件和使用 SSMS XEvent 探查器。

9、示例

9.1、借助派生的源表，使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作

下面的示例使用 MERGE 以更新或插入行的方式来修改 数据库中的 T_UnitMeasure表。

1
2
3
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;

当源表中的 NewCode 值与目标表T_UnitMeasure 的(UnitMeasureCode ) 列中的值匹配时，就会更新此目标表中的 NameInfo 列。 如果 NewCode 的值不匹配，就会将源行插入目标表中。 此源表是一个派生表，它使用 Transact-SQL 表值构造函数指定源表的多个行。 有关在派生表中使用表值构造函数的详细信息，请参阅表值构造函数 (Transact-SQL)。

OUTPUT 子句可用于查询 MERGE 语句的结果，有关详细信息，请参阅 OUTPUT 子句。 下面的示例还展示了如何在表变量中存储 OUTPUT 子句的结果。 然后，通过运行返回已插入行数和已更新行数的简单选择操作，汇总 MERGE 语句的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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;

查询目标表T_UnitMeasure返回的结果复合预期，Car00001=问界M5更新为“问界M9”，其他进行了插入操作。

9.2、使用 MERGE 在一个语句中对表执行 INSERT 和 UPDATE 操作

常见方案是，更新表中的一个或多个列（若有匹配行）。 或者，在没有匹配行的情况下，将数据作为新行插入。 处理两种方案之一的一般方法是，将参数传递给包含相应 UPDATE 和 INSERT 语句的存储过程。 借助 MERGE 语句，可以在一个语句中同时执行这两项任务。 下面的示例显示了数据库中一个同时包含 INSERT 语句和 UPDATE 语句的存储过程。 随后，过程被修改为，使用一个 MERGE 语句运行等效的操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
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 行受影响)

以上操作完成执行成功后，查询结果如下：

1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM T_UnitMeasure;
 
UnitMeasureCode NameInfo
--------------- -------------------------
Car00001        问界M7
Car00004        问界M9
Car00002        丰田皇冠
Car00003        奔驰E300L
Car00005        宝马X5
 
(5 行受影响)

9.3、使用 MERGE 在一个语句中对表执行 UPDATE 和 DELETE 操作

下面的示例使用 MERGE 根据 T_UnitMeasure 表中记录。 通过merge 更新 或删除 T_UnitMeasure 表数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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 语句的执行结果插入到另一个表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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 对图形数据库中的目标边缘表执行 INSERT 或 UPDATE 操作

在此示例中，创建节点表 Person 和 City 以及边缘表 livesIn。 如果 Person 和 City 之间尚不存在 livesIn 边缘，则对边缘使用 MERGE 语句，并插入新行。 如果已有边缘，只需更新 livesIn 边缘上的 StreetAddress 属性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- 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、相关内容

  • SELECT (Transact-SQL)

  • INSERT (Transact-SQL)

  • UPDATE (Transact-SQL)

  • DELETE (Transact-SQL)

  • OUTPUT 子句 (Transact-SQL)

  • 在 Integration Services 包中执行 MERGE

  • FROM (Transact-SQL)

  • 表值构造函数 (Transact-SQL)


相关文章

PostgreSQL自动递增2024-11-06
SQL中的INNER JOIN用法详解2024-11-29
SQL Server 数据太多怎么优化2024-11-29
SQL server 中 CROSS APPLY的操作2024-11-29
SQL Duplicate entry for key ‘PRIMARY‘主键重复报错怎么解决2024-11-29
SQL Server怎么创建用户只能访问指定数据库2024-11-29
SQL报错注入之updatexml2024-11-29
sql计算两个时间的时间差2024-11-29
SQL怎么创建临时表2024-11-29

Copyright © 2015-2024 广为博客. 闽ICP备2024075899号-1