Merge 与Left join

 /*

   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