T-SQL——关于数据合并(Merge)

目录

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,是可选的，可以是紧紧新增或仅仅删除
  • 目标表和源表可以是一个查询结果集

4.参考

posted @ 2023-09-12 23:38  shanzm  阅读(321)  评论(0)  编辑  收藏  举报
TOP