SQL Server MERGE(表跟表的結合)

以下例子可以說明MERGE用處
假設2表
一個是主要的總表           1 2 3 4 5 6 7  共7筆資料
另一表為要加入的新表   1 2 3 8 9       共5筆資料

其中，重複的要做更新，而新的資料要加進總表
這時一筆筆比對，一筆筆加入更新，頗累的
MARGE用途就在這裡
可以想像成總公司的資料
要結合子公司傳來的的資料
就可能會出現這個問題






老師還說個例子，選課系統
說明 WHEN NOT MATCHED 中的
BY TARGET 跟
BY SOURCE 的差別

假設上學期選課的有         2、4、5、7、10號同學
下學期同課程新的名單有 2、3、4、5、11號同學

2、4 、5號同學要更新 (WHEN MATCHED)
3、11號同學要新增      (WHEN NOT MATCHED BY SOURDE )
7、10號同學則刪除      (WHEN NOT MATCHED BY TARGET )

CREATE TABLE #客戶總表
(
  客戶編號 CHAR(5), 姓名 NCHAR(5), 電話 CHAR(20),
  地址 NCHAR(50), 異動時間 DATE
)
GO
CREATE TABLE #異動客戶
(
  客戶編號 CHAR(5), 姓名 NCHAR(5), 電話 CHAR(20),
  地址 NCHAR(50), 異動時間 DATE
)
GO

CREATE TABLE #同步客戶記錄
(
  同步動作 CHAR(10), 同步時間 SMALLDATETIME,
  客戶編號 CHAR(5), 舊姓名 NCHAR(5), 新姓名 NCHAR(5),
  舊電話 CHAR(20), 新電話 CHAR(20), 舊地址 NCHAR(50),
  新地址 NCHAR(50), 舊異動時間 DATE, 新異動時間 DATE
)
GO

TRUNCATE TABLE #客戶總表


INSERT INTO #客戶總表 VALUES
('A0001','王小明','03-3334444','桃園縣中壢市','2000-5-5'),
('B0104','李小英','02-22223333','台北市新莊市','2001-4-7'),
('A0013','林小雄','03-5556666','新竹縣竹北市','2002-7-4'),
('B0005','廖小美','04-24445555','台中市西屯區','2004-8-2'),
('A0015','ㄚ土伯','05-5556666','嘉義縣嘉義市','2004-8-2'),
('B0007','錢夫人','07-7778888','高雄市左營區','2004-8-2')

INSERT INTO #異動客戶 VALUES
('A0001','王大明','03-3225555','桃園縣平鎮市','2008-8-8'),
('B0104','李大英','02-22224444','台北市新店市','2008-4-7'),
('A0018','忍太郎','03-5558888','新竹縣新竹市','2007-7-8'),
('B0005','廖大美','04-24447777','台中市北屯區','2008-10-10'),
('A0025','撒隆巴斯','06-6667777','台南縣新營市','2007-8-2'),
('B0007','錢貴婦','07-7779999','高雄市旗津區','2006-12-31')
GO



SELECT * FROM #客戶總表
SELECT * FROM #異動客戶
SELECT * FROM #同步客戶記錄

MERGE #客戶總表 AS t
USING #異動客戶 AS s
ON s.客戶編號=t.客戶編號

   --當比對相符則執行更新(這一定要加AND)
WHEN MATCHED AND s.異動時間>t.異動時間 THEN 
 UPDATE SET t.姓名=s.姓名,t.電話=s.電話,t.地址=s.地址,t.異動時間=s.異動時間

    --當比對沒有相符時，則新增
    --可以配合 BY TARGET 或是 BY SOURECE    
WHEN NOT MATCHED THEN

    INSERT (客戶編號,姓名,電話,地址,異動時間)
      VALUES(s.客戶編號,s.姓名,s.電話,s.地址,s.異動時間)
    
    --以下是紀錄，可加可不加，加了每筆動作都會紀錄到#同步客戶紀錄
    --其中的$action是OUTPUT中針對MARGE的字句，會依當時的動作
    --在欄位中寫入'UPDATE'、'DELETE'、'INSERT'
    --若要使用，欄位格式要為CNAR(10)或以上
OUTPUT $action,GETDATE(),inserted.客戶編號,deleted.姓名,inserted.姓名,
 deleted.電話,inserted.電話,deleted.地址,inserted.地址,
 deleted.異動時間,inserted.異動時間
INTO #同步客戶記錄(同步動作, 同步時間, 客戶編號, 舊姓名,
 新姓名, 舊電話, 新電話, 舊地址, 新地址, 舊異動時間, 新異動時間);
   --整個MARGE會是一大段，要用分號結尾