SQL内的函数用法之merge into

merge into 基本用法格式

MERGE INTO TargetTable AS T           
USING SourceTable AS S                
   ON T.ID = S.ID                      
WHEN MATCHED         
   THEN UPDATE SET T.DSPT = S.DSPT  
WHEN NOT MATCHED BY TARGET   
   THEN INSERT VALUES(S.ID,S.DSPT)
-- 下面语句暂未实验成功，不确定正确性
WHEN NOT MATCHED BY SOURCE            
   THEN DELETE
OUTPUT $ACTION AS [ACTION],
   Deleted.ID AS 'Deleted ID',
   Deleted.DSPT AS 'Deleted Description',
   Inserted.ID AS 'Inserted ID',
   Inserted.DSPT AS 'Inserted Description'
INTO @Log;

• 原表（TargetTable 表）为主，修改的是该表内容。目标表为辅助，用以提供条件。
• merge中的output可以打印出操作记录。（暂未实验成功）

注意事项

两个表关联的条件（上述中的id），不能在下面语句中进行update，否则将会报错。