關於MERGE INTO

Posted on Edited on In Views: Disqus: Symbols count in article: 658 Reading time ≈ 1 mins.

關於MERGE INTO

這篇介紹MERGE INTO使用情境與如何使用。

使用情境

1
2
3
4
MERGE INTO 可以達成以下需求
如果Key MATCHED，可做 UPDATE/DELETE
如果Key MATCHED + Condition (AND field <> field)，可做 UPDATE/DELETE
如果Key NOT MATCHED，可INSERT

How to use

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MERGE INTO #tmp1
   USING #tmp2
   ON #tmp1.key1 = #tmp2.key1 //可以多組Key
WHEN MATCHED THEN   
    UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone 
WHEN MATCHED AND #tmp1.Name <> #tmp2.Name THEN  //MATCHED可以再加條件
    UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone 
WHEN MATCHED AND #tmp1.Name='' AND #tmp2.Name='' THEN  //MATCHED可以再加條件
    DELETE //DELETE只要這樣寫
WHEN NOT MATCHED THEN
    INSERT VALUES(#tmp2.co1,#tmp2.col2,#tmp2.col3);
	
(1) #tmp1:要被INSERT/UPDATE/DELETE的Table
(2) #tmp2:被參考的Table