shanzm-2023年9月12日 21:09:04
0. 背景说明及测试数据
什么是合并？
根据记录是否已经存在，决定是否插入、更新或删除
简单说明，数据库需要将某个表A的数据同步到指定的表B中，
表A和表B中字段一样，都有一样的唯一键,需要同时实现一下三种操作：
- 若表A中某条数据在表B中不存在，则在表中插入该条数据
- 若表A中某条数据在表B中存在，但是某些字段值不一样，则对表B进行更改
- 若表B中存在某条数据在表A中不存在，则删除表B中的该条记录
1. 直接清空，重新插入
- 简单粗暴：将表B清空，之后将表A中数据全部插入，即实现了上述三操作
- 此法并非万能，若是在数据库中清洗数据的时候可以这么做，但是程序中不建议
TRUNCATE TABLE tbB
INSERT INTO tbB SELECT * FROM tbA
2. 单条记录执行插入、更新操作
--创建一个临时测试数据
IF OBJECT_ID('tempdb..#temp') IS NOT NULL BEGIN
DROP TABLE #temp;
END;
CREATE TABLE #temp (Id INT, Name VARCHAR(10), CreateTime DATETIME);
INSERT INTO #temp(Id, Name, CreateTime)
VALUES(1, '张三', '2023-6-1 15:20:31'),
(2, '李四', '2023-6-1 15:25:53');
--不存在Id=2的记录则插入
IF NOT EXISTS (SELECT * FROM #temp WHERE Id=2)
BEGIN
INSERT INTO #temp(Id, Name, CreateTime)VALUES(2, '张三', GETDATE());
END;
--存在Id=2的记录则更新
ELSE
BEGIN
UPDATE #temp SET Name='李四', CreateTime=GETDATE()WHERE Id=2;
END;
SELECT * FROM #temp;
3. Merge函数
3.1 准备测试数据
IF OBJECT_ID('tempdb..#tempA') IS NOT NULL
DROP TABLE #tempA;
CREATE TABLE #tempA
(
[Id] INT,
[Name] VARCHAR(4),
[Msg] VARCHAR(100),
[CreateTime] DATETIME
);
INSERT INTO #tempA
(
[Id],
[Name],
[Msg],
[CreateTime]
)
VALUES
(1, '张三', '这是要插入的', N'2023-03-31'),
(2, '李四', '这是要更新的', N'2023-03-31');
SELECT * FROM #tempA;
--结果：
Id Name Msg CreateTime
----------- ---- -------------------- -----------------
1 张三 这是要插入的 2023-03-31
2 李四 这是要更新的 2023-03-31
IF OBJECT_ID('tempdb..#tempB') IS NOT NULL
DROP TABLE #tempB;
CREATE TABLE #tempB
(
[Id] INT,
[Name] VARCHAR(4),
[Msg] VARCHAR(100),
[CreateTime] DATETIME
);
INSERT INTO #tempB
(
[Id],
[Name],
[Msg],
[CreateTime]
)
VALUES
(2, '李四', '这是要被更新的', N'2023-01-31'),
(3, '张三', '这是要被删除的', N'2023-01-31');
SELECT * FROM #tempB;
--结果：
Id Name Msg CreateTime
----------- ---- -------------------- ----------------
2 李四 这是要被更新的 2023-01-31
3 张三 这是要被删除的 2023-01-31
3.2 测试Merge
--没要合并操作前的数据
SELECT * FROM #tempB
MERGE INTO #tempB AS T--目标表
USING #tempA AS S--源表
ON T.Id=S.Id
WHEN MATCHED --当满足 T.Id=S.Id条件时候
THEN UPDATE SET T.Name=S.Name, T.Msg=s.Msg,T.CreateTime=S.CreateTime
WHEN NOT MATCHED--当目标表中没有该Id，而源表中有，则插入
THEN INSERT VALUES(S.Id,S.Name, S.Msg,S.CreateTime)
WHEN NOT MATCHED BY SOURCE--当目标表中存在，源表中不存在，则删除
THEN DELETE;
--OUTPUT $action AS[ACTION],Inserted.Id AS [插入的Id],Inserted.Msg AS 插入的Msg,Deleted.Id AS 删除的Id,Deleted.Msg AS 删除的Msg;--输出各个操作
--合并操作后的数据
SELECT * FROM #tempB
--结果
--原始数据
Id Name Msg CreateTime
----------- ---- -------------------- -----------------------
2 李四 这是要被更新的 2023-01-31
3 王五 这是要被删除的 2023-01-31
--Merge后的数据
Id Name Msg CreateTime
----------- ---- -------------------- -----------------------
2 李四 这是要更新的 2023-03-31
1 张三 这是要插入的 2023-03-31
3.3 关于Merge
- 若原表中出现重复记录，而该记录是目标表中没有的，则会将所有的重复记录插入到目标中
- Merge关键字后面使用了多个WHEN……THEN,是可选的，可以是紧紧新增或仅仅删除
- 目标表和源表可以是一个查询结果集