SQL SERVER 中MERGE的使用

来源：互联网发布：mac os x lion 10.8.5 编辑：程序博客网时间：2024/05/21 03:20

需求，

1.删除A表中存在，B表中不存在的数据

2.添加A表中不存在，B表中存在的数据

3.修改A表与B表ID相等的记录，设置A.COL_VALUE = B.COL_VALUE;

CREATE TABLE EMPA(ID INT PRIMARY KEY NOT NULL,EMP_NAME VARCHAR(20));CREATE TABLE EMPB(ID INT PRIMARY KEY NOT NULL,EMP_NAME VARCHAR(20))INSERT INTO EMPA VALUES(1,'A');INSERT INTO EMPA VALUES(2,'B');INSERT INTO EMPA VALUES(3,'C');INSERT INTO EMPA VALUES(4,'E');INSERT INTO EMPA VALUES(5,'F');INSERT INTO EMPB VALUES(3,'E');INSERT INTO EMPB VALUES(4,'F');INSERT INTO EMPB VALUES(5,'G');SELECT * FROM EMPA;SELECT * FROM EMPB;MERGE EMPA AS TARGETUSING (SELECT * FROM EMPB) AS SOURCEON (TARGET.ID = SOURCE.ID)WHEN MATCHED THEN UPDATE SET TARGET.EMP_NAME = SOURCE.EMP_NAMEWHEN NOT MATCHED BY TARGETTHENINSERT (ID,EMP_NAME) VALUES(SOURCE.ID,SOURCE.EMP_NAME)WHEN NOT MATCHED BY SOURCETHEN DELETE ;SELECT * FROM EMPA;SELECT * FROM EMPB;