技術文章 » T-SQL——關於資料合併(Merge)

T-SQL——關於資料合併(Merge)

2023-09-13 06:00:21

目錄

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.參考