sqlserver数据库中存储过程实现跨数据库定时抓取数据

1、sql server数据库中存储过程实现跨数据库定时抓取数据set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDUREdbo.I6GetPurOrderFromCRMasbeginSETNOCOUNT ONselect *,ROW_NUMBER()OVER(ORDER BY orderid) AS rowNumber into #TempSenderCRMDetailTable from(select * fromopenquery(TOFANSKICRM,select b.* from ERPOrderDetailSync b whe

2、re feedback <> 1) as tempData declare max1 int select max1=max(rowNumber) from #TempSenderCRMDetailTable declare rowNo1 int setrowNo1=1 while rowNo1<=max1begindeclare dtlOrderId varchar(30)declaredtlLineId intdeclare dtlItemNo varchar(30)declare dtlRanks varchar(5)declaredtlMsUnit varchar(5

3、)declare dtlQtydecimal(18,8)declare dtlPrice decimal(18,8)declare dtlOrderSum decimal(18,8)declaredtlOrderCurType varchar(4)declaredtlexchgRate decimal(18,8)declaredtlReqDate datetimedeclare dtlDeliverDatedatetimedeclaredeclare dtlTaxRate decimal(18,8)declare dtlTaxSum decimal(18,8)dtlRemarks varcha

4、r(180)- declare dtlpriCodevarchar(10)declare dtlDiscount decimal(18,8)select dtlOrderId=orderid,dtlLineId=lineid,dtlItemNo=itemno, dtlRanks=ranks, dtlMsUnit=msunit,dtlQty=qty,dtlPrice=price,dtlOrderS um=ordersum,dtlOrderCurType=curtype, dtlexchgRate=exchgRate,dtlReqDate=reqdate,dtlDeliver Date=deliv

5、erdate,dtlTaxRate=taxrate, dtlTaxSum=taxsum,dtlRemarks=remarks,dtlDiscount=dis count from #TempSenderCRMDetailTable where rowNumber = rowNo1insert intoec_ordersdtl (orderid,lineid,orderno,itemno,ranks,msunit,qty,price,ordersum,c urtype,exchgrate,reqdate, deliverdate,taxrate,remarks,fprice,fordersum,

6、pricode,childflg,pa rentid,sqty,makebalflg,discrate,noprice,notaxordsum, ordhwsum,fordhwsum,notaxordhwsum,taxsum,notaxdissum,fno taxordsum,fnotaxordhwsum,ftaxsum,fnotaxdissum,fnoprice,disc ount,purseflg,sendflg,discsum,fdiscsum,unitchgn,chgbase,disdata,pricexs) values(dtlOrderId,dtlLineId,dtlOrderId

7、,dtlItemNo,dtl Ranks,dtlMsUnit,dtlQty,dtlPrice,dtlOrderSum,dtlOrd erCurType,dtlexchgRate, dtlReqDate,dtlDeliverDate,dtlTaxRate,dtlRemarks,dt lPrice,dtlOrderSum,026,0,0,dtlQty,0,dtlDiscount,round(i snull(dtlPrice,0)/1.17,8),round(dtlOrderSum/1.17 ,2),round(dtlOrderSum/dtlDisco unt,2),round(dtlOrderSu

8、m/dtlDiscount,2),round(isnull(dtl Qty,0)*isnull(dtlPrice,0)/1.17,2), round(dtlOrderSum/1.17*0.17,2),round(isnull(dtlQty,0)*isn ull(dtlPrice,0)/1.17-dtlOrderSum/1.17,2),round(dtlOrderS um/1.17,2), round(isnull(dtlQty,0)*isnull(dtlPrice,0)/1.17,2), round(dtlOrderSum-dtlOrderSum/1.17,2), round(isnull(d

9、tlQty,0)*isnull(dtlPrice,0)/1.17-dtlOrderSu m/1.17,2), round(isnull(dtlPrice,0)/1.17,8), isnull(dtlDiscount,0)*dtlPrice,0,0,round(dtlPrice*dtlQt y-dtlOrderSum,2),round(dtlPrice*dtlQty-dtlOrderSum, 2),1,1,0,1) -插入日志表 insert into crm_to_i6_log (dbtbname,orderid,dblineid,insertdate) values(ec_ordersdtl

10、,dtlOrderId,dtlLineId,getdate()- update crm feedback=1 declare dtlupdateC varchar(300) declare dtlselectC varchar(200) set dtlselectC = select * from ERPOrderDetailSync where orderId = +dtlOrderId+ and lineid = +convert(varchar,dtlLineId)+setdtlupdateC = update openquery(TOFANSKICRM, +dtlselectC+) s

11、et feedback = 1; exec(dtlupdateC) set rowNo1=rowNo1+1 end drop table #TempSenderCRMDetailTable select *,ROW_NUMBER() OVER(ORDER BY orderid) AS rowNumber into #TempCrmSenderTable from (select * from openquery(TOFANSKICRM,select * from ERPOrderInfoSync )where feedback <> 1) as tempData declare m

12、ax int select max=max(rowNumber) from #TempCrmSenderTable declare rowNo int set rowNo=1 while rowNo<=max begindeclare orderId varchar(30)declare orderNo varchar(30) declare recordDate datetime declare recordMan varchar(15) declare orderComp varchar(15) declare orderDept varchar(15) declare oCode

13、varchar(20) declare orderSum decimal(18,2) declare orderType varchar(5) declare salePre varchar(5) declare recComp varchar(20) declare recAddr varchar(180) declare appManvarchar(20) declare appDate datetime declare deliverAddr varchar(80)declare deliverDate datetimedeclare curType varchar(3) declare

14、 exchgRate decimal(18,8) declare ordStatus int declare disData int declare endStatus int declare docFrom char(1) declare linkMan varchar(20)declare cellPhonevarchar(50) declare listSum decimal(18,8) declare orderCatgy char(2)declare usercompvarchar(30) declare rundept varchar(15) declare runemp varc

15、har(15)- 取数据 selectorderId=orderId,recordDate=recordDate,recordMan=reco rdman,orderComp=ordercomp,orderDept=orderDept, oCode=ocode,orderSum=orderSum,orderType=orderTyp e,salePre=salepre,recComp=reccomp,recAddr=recAddr, appMan =appMan,appDate=appdate,deliverAddr=deliverAddr,del iverDate=deliverDate,c

16、urType=curType,exchgRate=exchgR ate, endStatus=ordStatus,disData=disdata,endStatus=endStatu s,docFrom=docFrom,linkMan=linkman,cellPhone=cellph one, listSum=listSum,orderCatgy=order_category from #TempCrmSenderTable where rowNumber = rowNoselect rundept = deptno,runemp=empno from fg_customfilewhere c

17、ompno=ordercompif(orderCatgy=02)beginset orderNo= F_SN+right(orderId,11)set usercomp = orderComp update ec_ordersdtl set orderno=orderNo where orderid=orderIdend elsebeginset orderNo = orderIdendif (orderType=01)beginset orderType =09endelse if (orderType=03)beginset orderType=01 insert intoendec_or

18、dersmst(orderid,orderno,recorddate,recordman,ordercomp, orderdept,ocode,ordersum,ordertype,salepre, reccomp,recaddr,appman,appdate,deliveraddr,deliverdate,curtyp e,exchgrate,ordstatus,disdata,endstatus,docfrom,fordhwsum, reclinkman,rectelephone,statcomp,user_001,paycomp,disctype,s endflg,appflg,endo

19、pt,priceget,sendwh,user_002,verdtm,rundept, runemp,usercomp,ordhwsum,orderdisc,discrate,fordersum,forde rdisc,purseflg,fnotaxordsum,fnotaxordhwsum,ftaxsum,fnotaxdis sum, notaxordsum,notaxordhwsum,taxsum,notaxdissum) values(orderId,orderNo,recordDate,recordMan,order Comp,orderDept,oCode,orderSum,orde

20、rType,salePre ,recComp,recAddr,appMan,appDate,deliverAddr,deliverDate, curType,exchgRate,endStatus,1,endStatus,docFrom, listSum,linkMan,cellPhone,orderComp,sage,orderComp,0,0,1, 99,0,1001,orderCatgy,getdate(),rundept,runemp,userco mp,listsum,listsum-ordersum,ordersum/listsum, orderSum,listsum-orders

21、um,0,round(ordersum/1.17,2), round(listsum-ordersum/1.17*0.17,2),round(ordersum/1.17*0.17,2),round(listsum-ordersum/1.17*0.17-ordersum/ 1.17-ordersum/1.17*0.17,2),round(ordersum/1.17,2),round(listsum/1.17,2),round(orde rsum/1.17*0.17,2),round(listsum-ordersum/1.17*0.17-or dersum/1.17-ordersum/1.17*0.17,2