SQL SERVER 中MERGE的使用

需求，

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 TARGET
USING (SELECT * FROM EMPB) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED 
	THEN UPDATE SET TARGET.EMP_NAME = SOURCE.EMP_NAME
WHEN NOT MATCHED BY TARGET
	THEN
	INSERT (ID,EMP_NAME) VALUES(SOURCE.ID,SOURCE.EMP_NAME)
WHEN NOT MATCHED BY SOURCE
	THEN DELETE ;

SELECT * FROM EMPA;
SELECT * FROM EMPB;