使用Merge語法來同步資料時小心誤砍了不該刪的資料

2016-02-04

日前用Merge語法同步當年度資料進歷史資料表，結果誤刪了所有歷史資料只剩當年度資料。雖然有Code Review但還是發生這樣的悲劇。

模擬一下當時環境，用下面的Code建立出兩張資料表。一張是當年度資料表Orders，而另一張是包含歷年資料的資料表HisOrders。

If Exists(Select 1 From SysObjects Where name='Orders') Drop Table Orders;
GO
If Exists(Select 1 From SysObjects Where name='HisOrders') Drop Table HisOrders;
GO
Create Table Orders([Year] Int,CustomerID Int,ProductID Int,Counts Int);
GO
Create Table HisOrders([Year] Int,CustomerID Int,ProductID Int,Counts Int);
GO
Create Clustered Index CIX_HisOrders On HisOrders([Year]);
GO
Insert Into Orders Values(105,1,1,10),(105,1,2,20),(105,1,3,10);
GO 
Insert Into HisOrders 
Values(102,1,1,10),(103,1,2,20),(104,1,3,10),(105,4,1,20),(105,5,2,10),(105,6,3,20);
GO

我在Orders塞3筆年度為105的資料，而HisOrders中則還有102，103，104等三筆其他年度資料。如下圖所示，HisOrders中目前已有6筆資料。

發生悲劇的Merge Code如下。

Merge HisOrders AS T
Using Orders S
ON T.[Year]=S.[Year] and T.CustomerID=S.CustomerID and T.ProductID=S.ProductID
When Not Matched By Target
 Then Insert([Year],CustomerID,ProductID,Counts) Values(S.[Year],S.CustomerID,S.ProductID,S.Counts)
When Matched And T.Counts<>S.Counts
 Then Update Set T.Counts=S.Counts
When Not Matched By Source
 Then Delete;

執行完上面的Code後我們看看HisOrders狀況(如下圖)，HisOrders居然只剩下105年度的資料。其他年度的資料都被我刪光了....Orz。

相信您比我聰明，已經看出問題點了，那就是我在When Not Matched By Source這一句語法後面漏加了年度的條件式，導致其他年度全被砍了。

正確寫法應該是這樣，如下圖的紅色圈選處在Delete的判斷式中要加入年度的條件。

Merge HisOrders AS T
Using Orders S
ON T.[Year]=S.[Year] and T.CustomerID=S.CustomerID and T.ProductID=S.ProductID
When Not Matched By Target
 Then Insert([Year],CustomerID,ProductID,Counts) Values(S.[Year],S.CustomerID,S.ProductID,S.Counts)
When Matched And T.Counts<>S.Counts
 Then Update Set T.Counts=S.Counts
When Not Matched By Source And T.[Year]=105
 Then Delete;

您也可以用CTE的方式來搭配Merge，如下圖紅色圈選處，利用這一種方式則在Delete的判斷式中不需要加入年度的條件。

With HisOrders_tmp AS(Select * From HisOrders Where [Year]=105)
Merge HisOrders_tmp AS T
Using Orders S
ON T.[Year]=S.[Year] and T.CustomerID=S.CustomerID and T.ProductID=S.ProductID
When Not Matched By Target
 Then Insert([Year],CustomerID,ProductID,Counts) Values(S.[Year],S.CustomerID,S.ProductID,S.Counts)
When Matched And T.Counts<>S.Counts
 Then Update Set T.Counts=S.Counts
When Not Matched By Source
 Then Delete;