将Table1中存在而Table2中不存在的记录插入到Table2中
问题:将Table1中存在而Table2中不存在的记录插入到Table2中,用一句SQL写出?
该问题的重点在于 Except 的原理:个人理解,通俗讲就是 (A表的记录-B表的记录)的差值结果,但是去除重复的记录。也就是以左表为参照表,查询中返回右表中没有找到的所有非重复的记录。
测试代码:
create table #Test1 ( id int, name varchar(50) ); create table #Test2 ( id int, name varchar(50) ) go insert into #Test1 values (1,'Nicholas1'); insert into #Test1 values (1,'Nicholas1'); insert into #Test1 values (2,'Nicholas2'); insert into #Test1 values (3,'Nicholas3'); insert into #Test1 values (4,'Nicholas4'); insert into #Test2 values (1,'Mary1'); insert into #Test2 values (2,'Nicholas2'); insert into #Test2 values (3,'Mary3'); insert into #Test2 values (4,'Mary4'); insert into #Test2 values (4,'Nicholas4'); go select * from #Test1; select * from #Test2; insert into #Test2(id,name) select id,name from (select id,name from #Test1 except select id,name from #Test2) as table_Temp;--except筛选 select * from #Test1; select * from #Test2; go drop table #Test1; drop table #Test2;
执行结果: