sql插入数据时,避免插入重复数据

1.往表T_Mid_WaterIndexArea中插入数据

   insert into [dbo].[T_Mid_WaterIndexArea](AreaCode,Year,DO,CODMn,BOD5,COD,NH3N,TP,TN,CompositeIndex)
select c.AreaCode,c.Year,
--c.AreaName,
AVG(c.DO) DO,AVG(c.CODMn) CODMn,AVG(c.BOD5) BOD5,AVG(c.COD) COD,
AVG(c.NH3N) NH3N,AVG(c.TP) TP,AVG(c.TN) TN,AVG(c.CompositeIndex) CompositeIndex
  from
(select a.*,b.Year,b.DO,b.CODMn,b.BOD5,b.COD,b.NH3N,b.TP,b.TN,b.CompositeIndex 
from 
[dbo].[T_Bas_RiverSection] a
join [dbo].[T_Mid_WaterIndexSection] b
on a.SectionCode=b.SectionCode) c
group by c.AreaCode,c.Year


2.避免插入重复数据


   insert into [dbo].[T_Mid_WaterIndexArea](AreaCode,Year,DO,CODMn,BOD5,COD,NH3N,TP,TN,CompositeIndex)
select * from
(select c.AreaCode,c.Year,
--c.AreaName,
AVG(c.DO) DO,AVG(c.CODMn) CODMn,AVG(c.BOD5) BOD5,AVG(c.COD) COD,
AVG(c.NH3N) NH3N,AVG(c.TP) TP,AVG(c.TN) TN,AVG(c.CompositeIndex) CompositeIndex
  from
(select a.*,b.Year,b.DO,b.CODMn,b.BOD5,b.COD,b.NH3N,b.TP,b.TN,b.CompositeIndex 
from 
[dbo].[T_Bas_RiverSection] a
join [dbo].[T_Mid_WaterIndexSection] b
on a.SectionCode=b.SectionCode) c
group by c.AreaCode,c.Year) d
--判断数据库中是否存在重复数据,重复数据不插入
where not exists(select  * from T_Mid_WaterIndexArea  w where w.AreaCode=d.AreaCode and w.Year=d.Year)


相关推荐
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页