利用Merge語法Insert資料並抓取資料源欄位資料

2023-02-20

在系統需求中常常會有利用舊年度資料源來預先產出新年度資料，由於資料表正規化規則，因此往往PK都是流水碼或GUID，因此產出新舊碼的對照表是關鍵中的關鍵，以下簡單模擬我遇見的需求。

下面的TSQL簡易產出我們的LAB環境

--主表
Create Table MainTable(
	[MID] int Identity Primary Key Clustered,
	[ProdName] NvarChar(10)	
);
--關聯表
Create Table DetailTable(
	[DID] int Identity Primary Key Clustered,
	[MID] int,
	[Description] NvarChar(400)
);
--寫入資料
Insert Into MainTable([ProdName])
OutPut Inserted.MID,'Color:White' Into DetailTable
Values('Bicycle');

Insert Into MainTable([ProdName])
OutPut Inserted.MID,'Color:Black' Into DetailTable
Values('Car');

執行完上述指令後，我們利用MID欄位Join兩張資料表，Select出下圖資料

接下來模擬我們想複製 MainTable 中 ProdName=Car 的資料，由於是多表相互關聯，所以我們 MainTable 及 DetailTable 都得新增相對應的資料。

--建立新舊碼對照表
Create Table #tmp(MID int,OldMID int);

--在MainTable搜尋ProdName為Car的資料再寫回MainTable(複製資料的概念)
--同時將新增進入的資料取出其新增後取得的MID及原資料列MID(更名為OldMID)
--寫入暫存新舊碼對照表#tmp中
Insert Into MainTable
Output Inserted.MID,MT.MID AS OldMID INTO #tmp
Select ProdName From MainTable AS MT Where ProdName='Car';

--利用#tmp中OldMID去Join出DetailTable中MID=OldMID的資料
--然後一樣將取出的資料寫回DetailTable完成資料複製
With tb AS(
	Select t.MID,d.[Description] From DetailTable d
	Inner Join #tmp t On d.MID = t.OldMID
)
Insert Into DetailTable
Select * From tb;

上述程式碼是我整個複製流程的想法，但在步驟二中Insert Into MainTable發生了錯誤，如下圖所示。在Insert Into中使用Output子句時，只能抓取Inserted的資料，並無法同時Output我資料源的欄位資料出去@@。所以我TSQL中想取MT.MID AS OldMID INTO #tmp 會是錯誤的。

因此我們改個方法，用Merge來Insert資料，如下面程式碼

--建立新舊碼對照表
Create Table #tmp(MID int,OldMID int);

--在MainTable搜尋ProdName為Car的資料再寫回MainTable(複製資料的概念)
--同時將新增進入的資料取出其新增後取得的MID及原資料列MID(更名為OldMID)
--寫入暫存新舊碼對照表#tmp中
With tb AS(Select MID,ProdName From MainTable Where ProdName='Car')
Merge MainTable AS T
Using tb AS S ON(1 = 0)
WHEN NOT MATCHED BY TARGET
	Then Insert(ProdName) Values(S.ProdName)
Output Inserted.MID,S.MID AS OldMID INTO #tmp;

--利用#tmp中OldMID去Join出DetailTable中MID=OldMID的資料
--然後一樣將取出的資料寫回DetailTable完成資料複製
With tb AS(
	Select t.MID,d.[Description] From DetailTable d
	Inner Join #tmp t On d.MID = t.OldMID
)
Insert Into DetailTable
Select * From tb;

上面程式碼執行後我們就順利完成資料複製了，如下圖所示，現在有 MainTable 有兩筆 ProdName=Car 的資料了，其MID=2及3。而 DetailTable 也有產出一筆 MID=3 的關聯資料囉。