SQL Exists妙用

最新推荐文章于 2024-07-26 16:13:37 发布

原创最新推荐文章于 2024-07-26 16:13:37 发布 · 744 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #insert #date

Ms SQL Server 专栏收录该内容

115 篇文章

订阅专栏

说明：

以下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