/*
Author:James
Desc:Merge 与Left join 区别
Date:2013/7/30
*/
--tb_Employ:员工表 --tb_Depart:部门表
--1:left join
insert into 表
select * from tb_depart t1 left join tb_Employ t2
on t1.DepartNo = t2.DepartNo
where t2.DepartNo is null
--Merge
merge into tb_Employ t1
using tb_Depart t2 on t1.DepartNo = t2.DepartNo
when matched then
update set t1.Age =7
when not matched by target then
insert (EmployNo,EmployName,DepartNo,Age,Sex)
values('005','e','003',6,'男')
when not matched by source then delete
output $action as [action],
inserted.EmployNo ,inserted.EmployName ,inserted.DepartNo ,
inserted.Age,inserted.sex,deleted.EmployNo,deleted.EmployName,
deleted.DepartNo,deleted.Age,deleted.sex;
go
using tb_Depart t2 on t1.DepartNo = t2.DepartNo
when matched then
update set t1.Age =7
when not matched by target then
insert (EmployNo,EmployName,DepartNo,Age,Sex)
values('005','e','003',6,'男')
when not matched by source then delete
output $action as [action],
inserted.EmployNo ,inserted.EmployName ,inserted.DepartNo ,
inserted.Age,inserted.sex,deleted.EmployNo,deleted.EmployName,
deleted.DepartNo,deleted.Age,deleted.sex;
go
扫一扫
1070
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
