如何筛选出A表中不在B表里的数据,并且把不在B表里的数据添加到A表中

[wushuyao]

A 表
id name
1 1
2 2
3 3



B 表
id name
1 1
2 2
3 3
4 4
5 5


得到结果如下：

A表
id name
1 1
2 2
3 3
4 4
5 5


就还有35分了，全给了出来 大家帮个忙，在此谢谢各位。

[dawugui]

insert into a select * from b where id not in (select id from a)

[dawugui]

--如果只考虑ID

create table A(id int,name int)
insert into a values(1 ,1)
insert into a values(2 ,2)
insert into a values(3 ,3)
create table B(id int,name int)
insert into b values(1 ,1)
insert into b values(2 ,2)
insert into b values(3 ,3)
insert into b values(4 ,4)
insert into b values(5 ,5)
go

insert into a select * from b where id not in (select id from a)

select * from a

drop table a , b

/*
id          name        
----------- ----------- 
1           1
2           2
3           3
4           4
5           5

（所影响的行数为 5 行）


*/

--如果同时考虑ID,name.

create table A(id int,name int)
insert into a values(1 ,1)
insert into a values(2 ,2)
insert into a values(3 ,3)
create table B(id int,name int)
insert into b values(1 ,1)
insert into b values(2 ,2)
insert into b values(3 ,3)
insert into b values(4 ,4)
insert into b values(5 ,5)
go

insert into a select * from b where not exists (select 1 from a where a.id = b.id and a.name = b.name)

select * from a

drop table a , b

/*
id          name        
----------- ----------- 
1           1
2           2
3           3
4           4
5           5

（所影响的行数为 5 行）


*/

[东那个升]

insert a
select * from a
except
select * from b

[东那个升]

insert a
select * from b 
where not exists(select 1 from a where checksum(b.id,b.name) =checksum(id,name))

[东那个升]

[Quote=引用 3 楼 ldslove 的回复:]
SQL code
insert a
select * from a
except
select * from b
[/Quote]

insert a
select * from b
except
select * from a

[htl258_Tony]

insert a
select * from b
except
select * from a