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,否则将会报错。