两张表结构相同,A表里数据是这样的
id pikid
1 10008
1 10010
1 10036
1 10037
1 10040
3 10015
3 10016
3 10017
3 10018
3 10019
4 10001
4 10005
4 10012
4 10014
4 10020
4 10021
B表中数据
id pikid
1 10008
1 10010
1 10034
1 10035
1 10036
1 10037
1 10038
1 10039
1 10040
2 10047
3 10015
3 10016
3 10017
3 10018
3 10019
4 10001
现在需要把B表中的数据插入到A表,两个字段完全相同的不插入,不相同的插入。
这个语句怎么写啊?弄不明白了!
id pikid
1 10008
1 10010
1 10036
1 10037
1 10040
3 10015
3 10016
3 10017
3 10018
3 10019
4 10001
4 10005
4 10012
4 10014
4 10020
4 10021
B表中数据
id pikid
1 10008
1 10010
1 10034
1 10035
1 10036
1 10037
1 10038
1 10039
1 10040
2 10047
3 10015
3 10016
3 10017
3 10018
3 10019
4 10001
现在需要把B表中的数据插入到A表,两个字段完全相同的不插入,不相同的插入。
这个语句怎么写啊?弄不明白了!
insert into A select * from B where not exists(select 1 from A where id =B.id AND pikid = B.pikid)
insert into A select * from B where not exists(select 1 from A where id =B.id AND pikid = B.pikid)
where not exists(select top 1 id from A where id =B.id AND pikid = B.pikid)
insert into A(id,pikid) select id,pikid from B t1
where not exists(select top 1 id from A where id =t1.id AND pikid = t1.pikid)
select b.id,b.pikid
from a left join b
on a.id = b.id and a.pikid = b.pikid
where b.id is not null
insert into tb1 select 1,'10008'
union all select 1, '10010'
union all select 1, '10036'
union all select 1, '10037'
union all select 1 , '10040'
union all select 3 , '10015'
union all select 3 , '10016'
union all select 3 , '10017'
union all select 3 , '10018'
union all select 3 , '10019'
union all select 4 , '10001'
union all select 4 , '10005'
union all select 4 , '10012'
union all select 4 , '10014'
union all select 4 , '10020'
union all select 4 , '10021'
create table tb2 (id int,pikid nvarchar(5))
insert into tb2 select 1,'10008'
union all select 1, '10011'
union all select 1, '10036'
union all select 1, '10038'
union all select 1 , '10047'
union all select 2 , '10015'
union all select 2 , '10016'
union all select 3 , '10017'
union all select 3 , '10018'
union all select 3 , '10019'
union all select 4 , '10001'
union all select 4 , '10005'
union all select 5 , '10012'
union all select 5 , '10014'
union all select 5 , '10020'
union all select 5 , '10021'
insert into tb1
select *
from tb2
where not exists(select 1 from tb1 where tb1.id =tb2.id AND tb1.pikid = tb2.pikid)/*
insert into tb1
select tb2.id,tb2.pikid
from tb1 left join tb2
on tb1.id = tb2.id and tb1.pikid = tb2.pikid
where tb2.id is not null
*/
select * from tb1 order by id,pikid
drop table tb1,tb2
一楼的这个就很合适了