MERGE语句的使用（同步数据）

红太狼 2023-06-19 11:25 3阅读 0赞

最近学习了SQL Server这个牛逼轰轰的语法，个人认为很牛逼。  
**牛逼之处：** MERGE语法可以将简单的INSERT，DELETE，UPDATE语句融合到一条语句中。  
**使用要领：** MERGE语法必须作用于 **两个数据源（数据表，子查询，视图）** 之间  
**语句结构分析：**  
MERGE 目标表  
USING 数据源（数据表，子查询，视图）  
ON 匹配条件  
WHEN MATCH THEN 操作语句 **；（结束的这个分号必不可少）**  
这就是一个简单的MERGE语句。  
**案例分析（场景为数据同步）：**  
先来模拟一个场景：就清华大学吧，有一个内部级别很高的学生信息管理系统，每一名入校新生的详细信息都将被采集到这个系统中，这些信息存储在TB\_STUDENT\_DETAIL表中；学校还有一个学生成绩管理系统，这个系统中有一个表TB\_STUDENT，只存储了学生的基本信息，并且这些信息是固定时间从学生信息管理系统中同步过来的。

现在由于新生入校和误操作等原因，我们要把学生成绩管理系统的TB\_STUDENT表缺失的信息从学生信息管理系统的TB\_STUDENT\_DETAIL表中同步过来，现在两张表的数据如下：

## 数据初始化： ##

CREATE TABLE TB_STUDENT_DETAIL (No VARCHAR(10),NAME VARCHAR(100),AGE INT,ADDRESS VARCHAR(100),MOBILE VARCHAR(11))
    
    INSERT INTO TB_STUDENT_DETAIL VALUES('001','张三',23,'朝阳区','12345678901')
    INSERT INTO TB_STUDENT_DETAIL VALUES('002','李四',25,'海淀区','12345678901')
    INSERT INTO TB_STUDENT_DETAIL VALUES('003','王五',26,'西城区','12345678901')
    INSERT INTO TB_STUDENT_DETAIL VALUES('005','马六',25,'东城区','12345678901')
    
    CREATE TABLE TB_STUDENT (No VARCHAR(10),NAME VARCHAR(100),AGE INT)
    
    --INSERT INTO TB_STUDENT_DETAIL VALUES('001','张三',24)
    INSERT INTO TB_STUDENT VALUES('001','',24)
    INSERT INTO TB_STUDENT VALUES('002','',25)
    INSERT INTO TB_STUDENT VALUES('003','',26)
    INSERT INTO TB_STUDENT VALUES('004','宋小宝',12)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70]

## 需求一、部分信息缺失的学生信息补全（例如上图中的学号为001,002,003的学生姓名丢失，需要补全） ##

MERGE TB_STUDENT T				--目标表（需要执行插入、修改、删除操作的表）
    USING TB_STUDENT_DETAIL S		--数据源
    ON T.No=S.No					--匹配条件
    WHEN MATCHED THEN UPDATE SET T.NAME=S.NAME;		--需要修改的字段(数据源中有001,002,003,目标表中也有,则根据数据源中的NAME修改目标表的NAME字段)
    --执行这条语句后再次查询两张表的数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 1]  
可以看到TB\_STUDENT表中学号为001,002,003的学生的姓名已经被这条语句从TB\_STUDENT\_DETAIL表中同步了过来。

## 需求二、新生信息导入（TB\_STUDENT\_DETAIL表中，学号为005的马六同学的信息在TB\_STUDENT中根本不存在，我们需要INSERT进来） ##

MERGE TB_STUDENT T				--目标表（需要执行插入、修改、删除操作的表）
    USING TB_STUDENT_DETAIL S		--数据源
    ON T.No=S.No					--匹配条件
    WHEN NOT MATCHED THEN INSERT VALUES(S.No,S.NAME,S.AGE);		--数据源中有005,目标表中没有,则将这条学生信息插入到目标表中
    --执行这条语句后再次查询两张表的数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 2]  
可以看到TB\_STUDENT\_DETAIL表中的新生005马六同学已经被插入到TB\_STUDENT表中

## 需求三、将TB\_STUDENT\_DETAIL表中不存在而TB\_STUDENT表中存在的已退学学生004宋小宝同学的信息删除 ##

MERGE TB_STUDENT T				--目标表（需要执行插入、修改、删除操作的表）
    USING TB_STUDENT_DETAIL S		--数据源
    ON T.No=S.No					--匹配条件
    WHEN NOT MATCHED BY SOURCE THEN DELETE;		--数据源中没有004宋小宝,而目标表中有,该学生已退学，需要删除信息（本例中只有一条，其实只要目标表中有的，而数据源中没有的都将会被删除）
    --执行这条语句后再次查询两张表的数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 3]

## 终极需求、现在以上的三步操作分开执行显得很麻烦，而且执行效率不高，我们需要使用一条sql语句完成这三步操作 ##

这个时候MERGE语句的作用就发挥出来了，我们再次初始化数据到最初状态：

DROP TABLE TB_STUDENT 
    DROP TABLE TB_STUDENT_DETAIL
    
    CREATE TABLE TB_STUDENT_DETAIL (No VARCHAR(10),NAME VARCHAR(100),AGE INT,ADDRESS VARCHAR(100),MOBILE VARCHAR(11))
    
    INSERT INTO TB_STUDENT_DETAIL VALUES('001','张三',23,'朝阳区','12345678901')
    INSERT INTO TB_STUDENT_DETAIL VALUES('002','李四',25,'海淀区','12345678901')
    INSERT INTO TB_STUDENT_DETAIL VALUES('003','王五',26,'西城区','12345678901')
    INSERT INTO TB_STUDENT_DETAIL VALUES('005','马六',25,'东城区','12345678901')
    
    CREATE TABLE TB_STUDENT (No VARCHAR(10),NAME VARCHAR(100),AGE INT)
    
    --INSERT INTO TB_STUDENT_DETAIL VALUES(1,'张三',24)
    INSERT INTO TB_STUDENT VALUES('001','',24)
    INSERT INTO TB_STUDENT VALUES('002','',25)
    INSERT INTO TB_STUDENT VALUES('003','',26)
    INSERT INTO TB_STUDENT VALUES('004','宋小宝',12)
    
    SELECT * FROM TB_STUDENT_DETAIL
    SELECT * FROM TB_STUDENT
    --现在数据已经初始化完成，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 4]

## 现在将以上的UPDATE，INSERT，DELETE三条语句整合为一条MERGE语句： ##

MERGE TB_STUDENT T				--目标表（需要执行插入、修改、删除操作的表）
    USING TB_STUDENT_DETAIL S		--数据源
    ON T.No=S.No					--匹配条件
    WHEN MATCHED THEN UPDATE SET T.NAME=S.NAME		--需要修改的字段(数据源中有001,002,003,目标表中也有,则根据数据源中的NAME修改目标表的NAME字段)
    WHEN NOT MATCHED THEN INSERT VALUES(S.No,S.NAME,S.AGE)		--数据源中有005,目标表中没有,则将这条学生信息插入到目标表中
    WHEN NOT MATCHED BY SOURCE THEN DELETE;		--数据源中没有004宋小宝,而目标表中有,该学生已退学，需要删除信息（本例中只有一条，其实只要目标表中有的，而数据源中没有的都将会被删除）
    --执行这条语句后再次查询两张表的数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 5]

## 看一下MERGE语句的执行结果： ##

①目标表和数据源中都有的学号为001,002,003学生的NAME字段已经被从TB\_STUDENT\_DETAIL同步到了TB\_STUDENT表中。  
②数据源中的新生005马六同学在目标表TB\_STUDENT表中并没有，但是此刻已经被同步到了TB\_STUDENT表中。  
③目标表中的004宋小宝同学已经退学，在数据源中并没有，需要删除，语句执行后也已经删除了。

**语法分析**  
**MERGE** 目标表  
**USING** 数据源（数据表，子查询，视图）  
**ON** 匹配条件  
**WHEN MATCH THEN** 操作语句(数据源和目标数据匹配上的数据，可以像案例中执行update，也可以执行delete操作，例如案例中的001,002,003)  
**WHEN NOT MATCH THEN** 操作语句(也可以写成WHEN NOT MATCH BY TARGET THEN,指数据源中有的，而目标表中没有的数据，可以执行insert操作,例如案例中的005)  
**WHEN NOT MATCH BY SOURCE THEN** 操作语句(指数据源中没有，而目标表中有的数据，可以执行delete操作，例如案例中的004)  
**；** **（结束的这个分号必不可少）**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019120615581627.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191206160120831.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191206161959444.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191206163418207.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191206171136587.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191206171433599.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bl9sdW1pbmc=,size_16,color_FFFFFF,t_70