日期:2014-05-18  浏览次数:20524 次

insert into 如何不重复数据
现有两表
表a
  mid 门店
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
7 gg
8 hh

表b
  mid 门店
1 aa
2 bb
3 cc
7 gg
8 hh
9 ii
10 jj
11 kk
现从表b向表a添加数据.请问如何不会重复添加
想要的结果:
表a
  mid 门店
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
7 gg
8 hh
9 ii
10 jj
11 kk


------解决方案--------------------
SQL code

--try
insert into a select * from b where mid not in (select mid from a)

------解决方案--------------------
SQL code
--> 测试数据: #a
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (mid int,门店 varchar(2))
insert into #a
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd' union all
select 5,'ee' union all
select 6,'ff' union all
select 7,'gg' union all
select 8,'hh'
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (mid int,门店 varchar(2))
insert into #b
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 7,'gg' union all
select 8,'hh' union all
select 9,'ii' union all
select 10,'jj' union all
select 11,'kk';

-->如果门店不重复
insert into #a select * from #b where not exists (select 1 from #a where 门店=#b.门店)

select * from #a
/*
mid         门店
----------- ----
1           aa
2           bb
3           cc
4           dd
5           ee
6           ff
7           gg
8           hh
9           ii
10          jj
11          kk
*/