查询两个表,比较他们没列的值是否相同,如果不相同,将不相同的插入到另以个临时表中.
因为我要比较很多表,所以希望可以传表名.
这个要用sql写出来。
------解决方案--------------------
- SQL code
insert into temp select * from a except select * from binsert into temp select * from b except select * from a
------解决方案--------------------
- SQL code
id name age 2 null 18
------解决方案--------------------
------解决方案--------------------
- SQL code
--A，B 两个表--A：--id name age --1 张三 22--2 张四 21--B：--id name age --1 张三 22--2 张四 18--那么得到的结果应该是--在一个临时表中插入：--id name age --2 null 18if OBJECT_ID('temp')is not null drop table tempgo create table temp ( id int ,name varchar(50), age int )if OBJECT_ID('A')is not null drop table Ago create table A ( id int ,name varchar(50), age int )insert into A select 1 ,'张三', 22 union all select 2, '张四' ,21 if OBJECT_ID('B')is not null drop table Bgo create table B ( id int ,name varchar(50), age int )insert into B select 1 ,'张三', 22 union all select 2, '张四' ,18 insert into temp select * from A except select * from Bselect * from tempid name age----------- -------------------------------------------------- -----------2 张四 21(1 行受影响)
------解决方案--------------------
去重
- SQL code
insert into tempselect * from a except select * from bunionselect * from b except select * from b
------解决方案--------------------
- SQL code
--又分的幺妹--方法1select * into #t from a where 1<>1insert into #t select * from a except select * from binsert into #t select * from b except select * from a--方法2select * into #t from a where 1<>1SELECT * FROM (SELECT * FROM aaa UNION SELECT * FROM dbo.sc )AS a EXCEPT(select * from aaa INTERSECT SELECT * FROM dbo.sc)--方法还有很多种... 集合问题
------解决方案--------------------
- SQL code
--A，B 两个表--A：--id name age --1 张三 22--2 张四 21--B：--id name age --1 张三 22--2 张四 18--那么得到的结果应该是--在一个临时表中插入：--id name age --2 null 18if OBJECT_ID('temp')is not null drop table tempgo create table temp ( id int ,name varchar(50), age int )if OBJECT_ID('A')is not null drop table Ago create table A ( id int ,name varchar(50), age int )insert into A select 1 ,'张三', 22 union all select 2, '张四' ,21 if OBJECT_ID('B')is not null drop table Bgo create table B ( id int ,name varchar(50), age int )insert into B select 1 ,'张三', 22 union all select 2, '张四' ,18 insert into temp select id ,case when name IN(select name from B ) then null else name end as 'name' ,age from (select * from A except select * from B )s select * from temp id name age----------- -------------------------------------------------- -----------2 NULL 21(1 行受影响)
------解决方案--------------------
- SQL code
--A，B 两个表--A：--id name age --1 张三 22--2 张四 21--B：--id name age --1 张三 22--2 张四 18--那么得到的结果应该是--在一个临时表中插入：--id name age --2 null 18if OBJECT_ID('temp')is not null drop table tempgo create table temp ( id int ,name varchar(50), age int )if OBJECT_ID('A')is not null drop table Ago create table A ( id int ,name varchar(50), age int )insert into A select 1 ,'张三', 22 union all select 2, '张四' ,21 if OBJECT_ID('B')is not null drop table Bgo create table B ( id int ,name varchar(50), age int )insert into B select 1 ,'张三', 22 union all select 2, '张四' ,18 insert into temp select id ,case when name IN(select name from B ) then null else name end as 'name' ,age from (select * from A except select * from B )s select * from temp id name age----------- -------------------------------------------------- -----------2 NULL 21(1 行受影响)