两个字段相同的表,怎样把A表数据批量导入B表中,且自动跳过重复数据?该怎么解决
2013-08-23 13:59 小草生活 阅读(592) 评论(0) 编辑 收藏 举报两个字段相同的表,怎样把A表数据批量导入B表中,且自动跳过重复数据?该怎么解决
www.MyException.Cn 发布于:2012-03-24 14:00:46 浏览:19次
两个字段相同的表,怎样把A表数据批量导入B表中,且自动跳过重复数据? 两个字段相同的表,怎样把A表数据批量导入B表中,且自动跳过重复数据? 谢谢大家!!!
------解决方案-------------------------------------------------------- create table #a(id int,nam varchar(20)) create table #b(id int,nam varchar(20))
insert into #b select 1, 'a ' union select 1, 'b ' union select 2, 'b ' union select 2, 'a '
insert into #a select distinct * from #b select * from #a select * from #b drop table #a drop table #b ------解决方案-------------------------------------------------------- INSERT INTO tableB SELECT a.* FROM tableA as a LEFT JOIN tableB as b on a.ID = b.ID WHERE a.ID IS NULL ------解决方案-------------------------------------------------------- create table #t1(ID int) create table #t2(ID int)
insert #t1 select 1 union all select 2 union all select 3
insert #t2 select 4 union all select 2 union all select 6
insert into #t2 select #t1.ID from #t1 where #t1.ID not in(select ID from #t2 where #t2.ID=#t1.ID)
select * from #t2
drop table #t1 drop table #t2 ------解决方案-------------------------------------------------------- insert into tab select * from tab1 where tab1.id not in(select id from tab) ------解决方案-------------------------------------------------------- 两个字段相同的表,怎样把A表数据批量导入B表中,且自动跳过重复数据? 谢谢大家!!!
insert into b select * from a where id not in (select id from b)
------解决方案-------------------------------------------------------- create table #a(id int,nam varchar(20)) create table #b(id int,nam varchar(20))
insert into #b select 1, 'a ' union select 1, 'b ' union select 2, 'b ' union select 2, 'a '
insert into #a select distinct * from #b select * from #a select * from #b drop table #a drop table #b ------解决方案-------------------------------------------------------- INSERT INTO tableB SELECT a.* FROM tableA as a LEFT JOIN tableB as b on a.ID = b.ID WHERE a.ID IS NULL ------解决方案-------------------------------------------------------- create table #t1(ID int) create table #t2(ID int)
insert #t1 select 1 union all select 2 union all select 3
insert #t2 select 4 union all select 2 union all select 6
insert into #t2 select #t1.ID from #t1 where #t1.ID not in(select ID from #t2 where #t2.ID=#t1.ID)
select * from #t2
drop table #t1 drop table #t2 ------解决方案-------------------------------------------------------- insert into tab select * from tab1 where tab1.id not in(select id from tab) ------解决方案-------------------------------------------------------- 两个字段相同的表,怎样把A表数据批量导入B表中,且自动跳过重复数据? 谢谢大家!!!
insert into b select * from a where id not in (select id from b)