札克烘培坊

起因：

在寫Stored Procedure 的時候，想針對整個資料表進行新增、更新或刪除，

看到有人分享使用Merge，藉此機會記錄下來。

語法：

MERGE TableA AS T --Target 
USING TableB AS S --Source
ON (T.condition1 = S.condition1)
WHEN MATCHED THEN UPDATE SET T.column1 = S.column1 [, T.column2 = S.column2 ...] 
WHEN NOT MATCHED BY TARGET ( BY SOURCE ) THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

實作內容：

-- 建立 Temp Table #Supplier
CREATE TABLE #Supplier(
	SupplierId INT NOT NULL PRIMARY KEY,
	SupplierName NVARCHAR(10),
	SupplierAddress NVARCHAR(100),
	UpdateTimes INT 
)


-- 建立 Temp Table #SupplierMod
CREATE TABLE #SupplierMod(
	SupplierId INT NOT NULL PRIMARY KEY,
	SupplierName NVARCHAR(10),
	SupplierAddress NVARCHAR(100),
	UpdateTimes INT
)

新增、更新及刪除

--輸入資料至 #Supplier
INSERT INTO #Supplier (SupplierId, SupplierName, SupplierAddress , UpdateTimes) VALUES (1, 'Supplier1', 'City1', 0);

--輸入資料至 #SupplierMod
INSERT INTO #SupplierMod (SupplierId, SupplierName, SupplierAddress , UpdateTimes) VALUES (2, 'Supplier2', 'City2', 0);

新增 / 更新

1. BY TARGET：當 Condition 不符合時，會將Source Table Insert 到 Target Table。

MERGE #Supplier AS T -- Target
USING #SupplierMod AS S -- Source
ON T.SupplierId = S.SupplierId 
WHEN MATCHED THEN
	UPDATE SET  T.SupplierName = S.SupplierName
			  , T.SupplierAddress = S.SupplierAddress
			  , T.UpdateTimes = S.UpdateTimes
WHEN NOT MATCHED BY TARGET 
	THEN INSERT (SupplierId, SupplierName, SupplierAddress, UpdateTimes) VALUES (S.SupplierId, S.SupplierName, S.SupplierAddress, 0);

更新 /  刪除

2. BY SOURCE：依照 Source Table 為主，不符合 Condition 則將 Target Table 內刪除。

MERGE #Supplier AS T -- Target
USING #SupplierMod AS S -- Source
ON T.SupplierId = S.SupplierId 
WHEN MATCHED THEN
	UPDATE SET  T.SupplierName = S.SupplierName
			  , T.SupplierAddress = S.SupplierAddress
			  , T.UpdateTimes = S.UpdateTimes
WHEN NOT MATCHED BY SOURCE THEN DELETE; 

參考：

1. MSDN Merge