SQL SERVER 中的 Merge 使用

SQL Server 中的Merge应用

[TOC]

## 1. Merge 介绍

**定义：**Merge 是 DML 关键字，Merge 可以将 Insert、Update、Delete 语句合并成一条语句，根据与源表对比的结果，对<mark>目标表进行 CUD 操作</mark>，Merge 语句不会对源表进行操作

**语法：**

```SQL
MERGE INTO target_table               -- target_table 目标表
USING source_table                    -- source_table 对比源表
ON condition                          -- condition 目标表与对比表的匹配条件
WHEN MATCHED BY TARGET THEN           -- 匹配成功
operation                             -- 匹配成功后的操作，通常是Update
WHEN NOT MATCHED BY TARGET THEN       -- 匹配不到
operation;                            -- 匹配不到后的操作，是Insert
WHEN NOT MATCHED BY SOURCE THEN       -- 匹配不到
operation;                            -- 匹配不到后的操作，是Delete
```

### 1.1. 关键词解释

#### 1.1.1. ON

指定联接 source_table 与 target_table 以确定匹配位置所要满足的条件

#### 1.1.2. MATCHED

目标表与源表匹配成功时进行更新操作或者删除操作，这里不能使用新增语句

#### 1.1.3. NOT MATCHED

目标表与源表匹配不成功时

+ 源表有数据、目标表无数据，则进行插入操作；

+ 目标表有数据、源表无数据，则进行更新或者删除数据操作

> **Tips：《1.1.3. WHEN NOT MATCHED》**
 - 此类型的操作 `INSERT`/`DELETE`/`UPDATE` 子句各只能有一条
>  - 此类型的如果 `BY TARGET THEN DELETE` 是没有意义的，目标表无可操作的数据，因此一般都是 `NOT MATCHED BY SOURCE THEN DELETE` 删除源表的数据来保证数据的一致性

#### 1.1.4. BY TARGET

用于指定如何匹配目标表的行（在目标表中数据是否存在），以确定是对目标表的执行更新、插入还是删除操作。

- 指定目标表中的列，这些列用于定义匹配目标表行的条件

- BY TARGET 在语句中可以省略不写，默认就是 BY TARGET，BY TARGET 不能放在 MATCHED 之后

- BY TARGET 子句后面可以使用 AND 来匹配更细的条件，例如 WHEN NOT MATCHED BY TARGET AND target_table.COL1 <> source_table.COL1

#### 1.1.5. BY SOURCE

用于指定如何匹配源表的行（在源表中数据是否存在），以确定是对目标表的执行更新、插入还是删除操作。

- 指定源表中的列，这些列用于定义匹配源表行的条件

- BY SOURCE 不可以省略，BY SOURCE 不能放在 MATCHED 之后

- BY SOURCE 子句，也可以使用 AND 来匹配更细的条件，例如 WHEN NOT MATCHED BY SOURCE AND source_table.COL1 <> target_table.COL1

#### 1.1.6. OUTPUT

在 Merge 语句中可以使用关键字 OUTPUT 来返回本次 Merge 执行变更的信息，OUTPUT 的使用还会使用到下面的关键字

+ deleted : 表示在 DELETE 或 UPDATE 操作中被删除或被更新的行的原始值，deleted 表示被删除或更新前的行。

+ $action : 表示执行的操作类型，它的值可以是 'INSERT'、'DELETE' 或 'UPDATE'。

+ inserted : 表示在 INSERT 或 UPDATE 操作中插入或被更新的新值。inserted 表示被插入或更新后的行。

<hr/>

> **Tips：《1. Merge 介绍》 **
+ MERGE 语句中的 ON 并不是指目标表和源表联合查询后的集合，它可以看作是 WHERE ，是目标表和源表匹配的条件，因此在 Merge 中实现 CUD 时要知道目标表和源表的数据集，不然容易出现全表操作的风险，例如：目标表没有确定范围是全表时，这时去匹配范围内的源表，目标表不存在源表匹配的结果时对目标表进行删除操作，这可能就会对全表进行删除动作

[========]

## 2. Merge使用示例

点击[SQL Server 学习创建的数据库-CRM库](http://www.cylyccjy.com/blog/info-md.html/7380D1C6FB9585B9?type=195)创建测试使用的数据表和数据，在另外复制创建Customer表，命名为CustomerBak，做为Customer表的备份表，准备好之后重复执行下面的语句几次

### 2.1. 常规示例

**示例代码：**

```SQL
MERGE INTO dbo.CustomerBak AS tar
USING (SELECT * FROM dbo.Customer WHERE Sex = 0) AS src
ON tar.Name = src.Name
WHEN MATCHED THEN  -- 目标表和源表都存在时，将源表的数据更新给目标表
UPDATE SET tar.Name = src.Name, tar.Age = src.Age, tar.CreateTime = src.CreateTime, tar.Sex = src.Sex, tar.Intention = src.Intention
WHEN NOT MATCHED BY SOURCE THEN DELETE  -- 目标表存在，源表不存在时删除目标数据
WHEN NOT MATCHED THEN  -- 目标表不存在，源表存在时，往目标表中插入数据（这里默认省略了 BY TARGET）
  INSERT (Name, Age, CreateTime, Sex, Intention, Remark)
  VALUES (src.Name + '-H', src.Age, src.CreateTime, src.Sex, src.Intention, 'NOT MATCHED AND src.Intention = ''购房''');
```

**执行结果：**

|Id|Name|Age|Sex|CreateTime|Intention|Remark|
|--|--|--|--|--|--|--|
|33|甄氏-H|31|0|2023-11-28 09:29:52.997|投资|NOT MATCHED AND src.Intention = '购房'|
|34|司马氏-H|26|0|2023-11-28 09:29:52.997|购房|NOT MATCHED AND src.Intention = '购房'|

**结果分析：**这里目标表（CustomerBak）原本就是空表，所以 MATCHED 并没有任何执行，而 NOT MATCHED 时，执行删除目标表和添加目标表的操作，观察结果中的Id就可以看出数据是一直再变化的，这里对目标表的删除和插入没有语句上的顺序，即使把插入放在删除前面，目标表中仍然是删除旧记录插入新记录，因此感觉 Merge 语句执行CUD操作之前已经把匹配的结果存储到一个地方了，可能是内存。这样才保证了这条 Merge 语句的正常执行

### 2.2. 匹配成功后删除示例

```SQL
MERGE INTO dbo.CustomerBak AS tar
USING (SELECT * FROM dbo.Customer WHERE Sex = 0) AS src
ON tar.Name = src.Name + '-H'
WHEN MATCHED THEN DELETE;  -- 匹配成功后删除目标表数据
```

**执行结果：**验证了数据匹配成功后，除了可以对目标进行更新操作还可以都目标表进行删除操作

### 2.3. 变更记录输出示例

**示例代码：**

```SQL
-- 创建一张临时表存放变更记录信息
CREATE TABLE #MyTempTable (
  delId      INT,
  delName    NVARCHAR(50),
  [action]   NVARCHAR(50),
  insertId   INT,
  insertName NVARCHAR(50)
);
GO

MERGE INTO dbo.CustomerBak AS tar
USING (SELECT * FROM dbo.Customer WHERE Sex = 0) AS src
ON tar.Name = src.Name
--WHEN MATCHED THEN DELETE;
WHEN MATCHED THEN UPDATE SET tar.Name = src.Name, tar.Age = src.Age, tar.CreateTime = src.CreateTime, tar.Sex = src.Sex, tar.Intention = src.Intention
WHEN NOT MATCHED THEN INSERT (Name, Age, CreateTime, Sex, Intention, Remark)
                      VALUES (src.Name + '-H', src.Age, src.CreateTime, src.Sex, src.Intention, 'NOT MATCHED AND src.Intention = ''购房''')
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT Deleted.Id, Deleted.Name, $action, Inserted.Id, Inserted.Name  -- 将变更的信息存放到临时表中
INTO #MyTempTable;

SELECT * FROM #MyTempTable  -- 输出变更的信息
DROP TABLE #MyTempTable;
```

**执行结果：**

**结果分析：**上面的代码分别执行了你删除和插入的操作，$action 参数表示的是数据变更的类型，如果上面的 Merge 语句有更新操作的话，那么 $action = 'UPDATE'

<hr/>

> **Tips：《2. Merge使用示例》**
示例代码中注意Name的变化，'-H'有无要看仔细

[========]

## 3. 常见错误

|序号|ERROR|PLAN|
|--|--|--|
|1|MERGE 语句的 'WHEN NOT MATCHED' 子句中不允许 'DELETE' 类型的操作。|出现上述错误是因为试图对无数据的无效操作，例如：`WHEN NOT MATCHED BY TARGET THEN DELETE`，基于目标表数据不存在执行删除操作，就无需去操作删除动作了，正确的写法应该是 `WHEN NOT MATCHED BY SOURCE THEN DELETE` 目标表中存在，源表不存在则删除目标表数据|
|2|An action of type 'Delete'/'UPDATE'/'INSERT' is not allowed in the "WHEN NOT MATCHED' clause of a MERGE statement.|Merge语句中 `WHEN NOT MATCHED` 只能存在一条INSERT/UPDATE/DELET|
|3|An action of type INSERT' is not allowed in WHEN SOURCE NOT MATCHED' or 'MATCHED' clauses of a MERGE statement.|Merge语句中 INSERT 不能放在匹配成功（MATCHED）的条件下执行 |
|4|'WHEN MATCHED' 类型的操作在 MERGE 语句的 'UPDATE' 子句中不能出现一次以上。|在操作语句中去判断值的不同，例如：`WHEN MATCHED AND src.Age < 30` 、 `WHEN MATCHED AND src.Age > 30`这样两条 MATCHED 语句可以改成`Update SET tar.Remark = CASE WHEN src.Age > 30 THEN '大于30岁' WHEN src.Age < 30 THEN '小于30岁' ELSE '' END`|
|5|MERGE 语句的 'WHEN NOT MATCHED' 子句中不允许 'UPDATE' 类型的操作。|和序号1的错误类似，没有匹配上，无数据可以修改|

[========]

## 参考资料

> 微软官方：https://learn.microsoft.com/zh-cn/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN