SQL Exists妙用

说明：

   以下SP表存在sp_id，sp_mdid,sp_gys,sp_txm的联合主键

 

一、以SP为准，将该表不存在的数据从ecv_spinfo 插入

 

insert into [192.192.192.249].wit_oa_scm.dbo.SP(sp_mdid,sp_id,sp_gys,sp_mc,sp_dw,sp_gg,sp_xz,sp_hsjj,sp_sl,sp_pp ,sp_bz2 ,sp_lb ,sp_txm,sp_yntt,sp_ynxp                                                                        ,sp_ynly                                                    ,sp_bz    ,add_date,sp_bc)
       select                                          '0001' ,spbm , sjbh ,spmc ,dw   ,gg   ,wlbzs,pjjj   ,sl   ,ppbmid,superbm,dlbmid,left(ltrim(spsmm),13) as spsmm,ztbz   ,case when datediff(month,tt.yjrq,getdate())<=1 then 'T' else 'F' end as sp_ynxp, case tt.jxlxid when '04' then 'T' else 'F' end  as sp_ynly,'',       getdate(),1
       from eSCashSystem.dbo.ecv_spinfo  tt
       where not exists(select * from  [192.192.192.249].wit_oa_scm.dbo.SP ta where tt.spbm=ta.sp_id and tt.sjbh=ta.sp_gys and ta.sp_mdid='0001' )
       and  convert(char(10),tt.ztrq,21)>=@v_DtBegin  and convert(char(10),tt.ztrq,21)<=@v_DtEnd

二、以ecv_spinfo 为准，将SP表不存在的数据从ecv_spinfo 插入

insert into [192.192.192.249].wit_oa_scm.dbo.SP(sp_mdid,sp_id,sp_gys,sp_mc,sp_dw,sp_gg,sp_xz,sp_hsjj,sp_sl,sp_pp ,sp_bz2 ,sp_lb ,sp_txm,sp_yntt,sp_ynxp                                                                        ,sp_ynly                                                    ,sp_bz    ,add_date,sp_bc)
       select                                          '0001' ,spbm , sjbh ,spmc ,dw   ,gg   ,wlbzs,pjjj   ,sl   ,ppbmid,superbm,dlbmid,left(ltrim(spsmm),13) as spsmm,ztbz   ,case when datediff(month,tt.yjrq,getdate())<=1 then 'T' else 'F' end as sp_ynxp, case tt.jxlxid when '04' then 'T' else 'F' end  as sp_ynly,'',       getdate(),1
       from [192.192.192.249].wit_oa_scm.dbo.SP    tt
       where not exists(select * from eSCashSystem.dbo.ecv_spinfo ta where tt.spbm=ta.sp_id and tt.sjbh=ta.sp_gys and tt.sp_mdid='0001' )
       and  convert(char(10),ta.ztrq,21)>=@v_DtBegin  and convert(char(10),ta.ztrq,21)<=@v_DtEnd