merge 可以做到
如果key相同,就update/DELETE
如果key相同 + 條件( and field<>field),就update/DELETE
如果key不存在,就insert

MERGE INTO #tmp1 --要被insert/update/delete的表
   USING #tmp2 --被參考的表
   ON #tmp1.key1 = #tmp2.key1 
   --(如果有多key)
   ON #tmp1.key1 = #tmp2.key1 and #tmp1.key2 = #tmp2.key2
WHEN MATCHED THEN   
    UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone 
WHEN MATCHED and #tmp1.Name<>#tmp2.Name THEN  --除了match可以再加條件 
    UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone 
WHEN MATCHED and #tmp1.Name='' AND #tmp2.Name='' THEN  --除了match可以再加條件 
    DELETE --DELETE只要這樣下就可以了
WHEN NOT MATCHED THEN
    INSERT VALUES(#tmp2.co1, #tmp2.col2,#tmp2.col3);

以tmp1為主,把有在tmp2的資料,新增到tmp1

select * into #tmp1 from 
(
select co1=1,col2=2,col3=3 
union
select co1=1,col2=2,col3=4 
union
select co1=1,col2=2,col3=5
)t0

select * into #tmp2 from 
(
select co1=1,col2=2,col3=3 
union
select co1=1,col2=2,col3=4 
union
select co1=1,col2=2,col3=6
)t0

--1.找出tmp1沒有
SELECT  * from #tmp2
EXCEPT	--在ORACLE的話，用MINUS
SELECT * FROM #tmp1

--2.tmp1沒有,就把它insert到tmp1去
MERGE INTO #tmp1
   USING #tmp2
   ON #tmp1.co1 = #tmp2.co1 and #tmp1.col2 = #tmp2.col2 and  #tmp1.col3 = #tmp2.col3 
WHEN NOT MATCHED THEN
   INSERT VALUES(#tmp2.co1, #tmp2.col2,#tmp2.col3);
   
--chk #tmp1是否已新增,1,2,6
select * from #tmp1
select * from #tmp2

以tmp1為主,把有在tmp2的資料,更新回tmp1/新增回tmp1

select * into #tmp1 from 
(
select ID=1,Name=cast( '' as varchar(30)),Phone=3 
union
select ID=2,Name='',Phone=4 
union
select ID=3,Name='',Phone=5
)t0

select * into #tmp2 from 
(
select ID=1,Name='Wiz Khalifa',Phone=30 
union
select ID=2,Name='Charlie Puth',Phone=40 
union
select ID=3,Name='Furious',Phone=50
union
select ID=4,Name='Soundtrack',Phone=60
)t0

--1.找出tmp1沒有
SELECT  * from #tmp2
EXCEPT	--在ORACLE的話，用MINUS
SELECT * FROM #tmp1

--2.tmp1沒有,就把它insert到tmp1去
MERGE INTO #tmp1
   USING #tmp2
   ON #tmp1.ID = #tmp2.ID  
WHEN MATCHED and (#tmp1.Name !=#tmp2.Name or #tmp1.Phone!=#tmp2.Phone) THEN
	UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone 
WHEN NOT MATCHED THEN
   INSERT VALUES(#tmp2.ID, #tmp2.Name,#tmp2.Phone);

--chk #tmp1.Name & #tmp1.Phone是否已更新 缺的資料ID=4是否已進入
select * from #tmp1 
select * from #tmp2

注意:如果是用update or delete不能多行.mean 要有唯一的key.
sql吐出的錯誤訊息為:MERGE 陳述式嘗試多次 UPDATE 或 DELETE 同一個資料列。這在目標資料列符合多個來源資料列時便會發生。MERGE 陳述式不能多次 UPDATE/DELETE 目標資料表的同一個資料列。請重新定義 ON 子句，以確定目標資料列最多只符合一個來源資料列，或使用 GROUP BY 子句將來源資料列編成群組。

ref
like 對2個庫存資料的解決方法.
https://dotblogs.com.tw/dc690216/2010/01/25/13313