SQL Server Merge语句

SQL Server MERGE语句简介

假设有两个表名为：source表和target表，并且需要根据source表中匹配的值更新target表。 有三种情况：

  • source表有一些target表中不存在的行。在这种情况下，需要将source表中的行插入到target表中。
  • target表有一些source表中不存在的行。 在这种情况下，需要从target表中删除行。
  • source表的某些行具有与target表中的行相同的键。 但是，这些行在非键列中具有不同的值。 在这种情况下，需要使用来自source表的值更新target表中的行。

下图说明了source表和target表以及相应的操作：插入，更新和删除：
SQL Server MERGE语句 - 图1
如果单独使用INSERT，UPDATE和DELETE语句，则必须构造三个单独的语句，以使用source表中的匹配行将数据更新到target表。
但是，SQL Server提供MERGE语句以用于同时执行三个操作。 以下是MERGE语句的语法：

  1. MERGE target_table USING source_table
  2. ON merge_condition
  3. WHEN MATCHED
  4.     THEN update_statement
  5. WHEN NOT MATCHED
  6.     THEN insert_statement
  7. WHEN NOT MATCHED BY SOURCE
  8.     THEN DELETE;
  9. SQL

首先，在MERGE子句中指定source_table表和target_table表。
其次，merge_condition确定source_table表中的行如何与target_table表中的行匹配。 它类似于join子句中的join条件。 通常，使用主键或唯一键的键列进行匹配。
第三，merge_condition有三种状态：MATCHED，NOT MATCHED和NOT MATCHED BY SOURCE。

  • MATCHED：这些是与合并条件匹配的行。 在图中，它们显示为蓝色。 对于匹配的行，需要使用source_table表中的值更新target_table表中的行列。
  • NOT MATCHED：这些是source_table表中的行，target_table表中没有任何匹配的行。 在图中，它们显示为橙色。 在这种情况下，需要将source_table表中的行添加到target_table表。 请注意，NOT MATCHED BY TARGET也称为目标不匹配。

  • NO MATCHED BY SOURCE：这些是target_table表中与source_table表中的任何行都不匹配的行。 它们在图中显示为绿色。 如果要将target_table表与source_table表中的数据同步，则需要使用此匹配条件从target_table表中删除行。

    SQL Server MERGE语句示例

    假设有两个表：sales.category和sales.category_staging，它们按产品类别存储销售额。参考以下创建语句：

    1. CREATE TABLE sales.category (
    2.   category_id INT PRIMARY KEY,
    3.   category_name VARCHAR(255) NOT NULL,
    4.   amount DECIMAL(10 , 2 )
    5. );
    6. INSERT INTO sales.category(category_id, category_name, amount)
    7. VALUES(1,'Children Bicycles',15000),
    8.   (2,'Comfort Bicycles',25000),
    9.   (3,'Cruisers Bicycles',13000),
    10.   (4,'Cyclocross Bicycles',10000);
    11. CREATE TABLE sales.category_staging (
    12.   category_id INT PRIMARY KEY,
    13.   category_name VARCHAR(255) NOT NULL,
    14.   amount DECIMAL(10 , 2 )
    15. );
    16. INSERT INTO sales.category_staging(category_id, category_name, amount)
    17. VALUES(1,'Children Bicycles',15000),
    18.   (3,'Cruisers Bicycles',13000),
    19.   (4,'Cyclocross Bicycles',20000),
    20.   (5,'Electric Bikes',10000),
    21.   (6,'Mountain Bikes',10000);
    22. SQL

    要使用sales.category_staging(源表)中的值将数据更新到sales.category(目标表)，请使用以下MERGE语句：

    1. MERGE sales.category t 
    2.   USING sales.category_staging s
    3. ON (s.category_id = t.category_id)
    4. WHEN MATCHED
    5.   THEN UPDATE SET 
    6.       t.category_name = s.category_name,
    7.       t.amount = s.amount
    8. WHEN NOT MATCHED BY TARGET 
    9.   THEN INSERT (category_id, category_name, amount)
    10.        VALUES (s.category_id, s.category_name, s.amount)
    11. WHEN NOT MATCHED BY SOURCE 
    12.   THEN DELETE;
    13. SQL

    执行过程如下图所示 -
    SQL Server MERGE语句 - 图2
    在此示例中，使用两个表中category_id列中的值作为合并条件。

  • 首先，sales.category_staging表中id值为1,3,4的行与目标表中的行匹配，因此，MERGE语句更新sales.category表中类别名称和amount列中的值。

  • 其次，sales.category_staging表中id值为5和6的行在sales.category表中不存在，因此MERGE语句将这些行插入到目标表中。
  • 第三，sales.sales_staging表中不存在sales.category表中具有id值为2的行，因此，MERGE语句将删除此行。

在合并的结果中，sales.category表中的数据与sales.category_staging表中的数据完全同步。
//原文出自【易百教程】，商业转载请联系作者获得授权，非商业转载请保留原文链接：https://www.yiibai.com/sqlserver/sql-server-merge.html