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

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[I6GetPurOrderFromCRM]

as

begin

 SET NOCOUNT ON

    select *,ROW_NUMBER()

 OVER(ORDER BY orderid) AS rowNumber into #TempSenderCRMDetailTable from

      (select * from openquery(TOFANSKICRM,'select b.* from ERPOrderDetailSync b where  feedback <> 1')) as tempData

 declare @max1 int

 select @max1=max(rowNumber) from #TempSenderCRMDetailTable

 declare @rowNo1 int

 set @rowNo1=1

 while @rowNo1<=@max1

  begin

            declare @dtlOrderId varchar(30)

            declare @dtlLineId int

            declare @dtlItemNo varchar(30)         

            declare @dtlRanks varchar(5)

            declare @dtlMsUnit varchar(5)

            declare @dtlQty decimal(18,8)

            declare @dtlPrice decimal(18,8)

            declare @dtlOrderSum decimal(18,8)

            declare @dtlOrderCurType varchar(4)

            declare @dtlexchgRate decimal(18,8)

            declare @dtlReqDate datetime

            declare @dtlDeliverDate datetime

            declare @dtlTaxRate decimal(18,8)

            declare @dtlTaxSum decimal(18,8)

            declare @dtlRemarks varchar(180)

           -- declare @dtlpriCode varchar(10)

            declare @dtlDiscount decimal(18,8)

            select  @dtlOrderId=orderid,@dtlLineId=lineid,@dtlItemNo=itemno,@dtlRanks=ranks,

            @dtlMsUnit=msunit,@dtlQty=qty,@dtlPrice=price,@dtlOrderSum=ordersum,@dtlOrderCurType=curtype,

            @dtlexchgRate=exchgRate,@dtlReqDate=reqdate,@dtlDeliverDate=deliverdate,@dtlTaxRate=taxrate,

            @dtlTaxSum=taxsum,@dtlRemarks=remarks,@dtlDiscount=discount from #TempSenderCRMDetailTable

            where rowNumber = @rowNo1

            insert into ec_ordersdtl (orderid,lineid,orderno,itemno,ranks,msunit,qty,price,ordersum,curtype,exchgrate,reqdate,

            deliverdate,taxrate,remarks,fprice,fordersum,pricode,childflg,parentid,sqty,makebalflg,discrate,noprice,notaxordsum,

            ordhwsum,fordhwsum,notaxordhwsum,taxsum,notaxdissum,fnotaxordsum,fnotaxordhwsum,ftaxsum,fnotaxdissum,fnoprice,discount,purseflg,sendflg,

            discsum,fdiscsum,unitchgn,chgbase,disdata,pricexs)

            values(@dtlOrderId,@dtlLineId,@dtlOrderId,@dtlItemNo,@dtlRanks,@dtlMsUnit,@dtlQty,@dtlPrice,@dtlOrderSum,@dtlOrderCurType,@dtlexchgRate,

            @dtlReqDate,@dtlDeliverDate,@dtlTaxRate,@dtlRemarks,@dtlPrice,@dtlOrderSum,'026',0,0,@dtlQty,0,@dtlDiscount,round(isnull(@dtlPrice,0)/1.17,8),

            round(@dtlOrderSum/1.17 ,2),round(@dtlOrderSum/@dtlDiscount,2),round(@dtlOrderSum/@dtlDiscount,2),round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17,2),

            round(@dtlOrderSum/1.17*0.17,2),round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17-@dtlOrderSum/1.17,2),round(@dtlOrderSum/1.17,2),

            round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17,2), round(@dtlOrderSum-@dtlOrderSum/1.17,2),

            round(isnull(@dtlQty,0)*isnull(@dtlPrice,0)/1.17-@dtlOrderSum/1.17,2), round(isnull(@dtlPrice,0)/1.17,8),

            isnull(@dtlDiscount,0)*@dtlPrice,0,0,round(@dtlPrice*@dtlQty-@dtlOrderSum,2),round(@dtlPrice*@dtlQty-@dtlOrderSum,2),1,1,0,1)

            --插入日志表

            insert into crm_to_i6_log (dbtbname,orderid,dblineid,insertdate) values('ec_ordersdtl',@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)+''''

   set @dtlupdateC = 'update openquery(TOFANSKICRM, '+@dtlselectC+') set 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 @max int

 select @max=max(rowNumber) from #TempCrmSenderTable

 declare @rowNo int

 set @rowNo=1

 while @rowNo<=@max

  begin

   declare @orderId varchar(30)

            declare @orderNo varchar(30)

   declare @recordDate datetime

   declare @recordMan varchar(15)

   declare @orderComp varchar(15)

   declare @orderDept varchar(15)

   declare @oCode varchar(20)

   declare @orderSum decimal(18,2)

   declare @orderType varchar(5)

   declare @salePre varchar(5)

   declare @recComp varchar(20)

   declare @recAddr varchar(180)

   declare @appMan varchar(20)

   declare @appDate datetime

   declare @deliverAddr varchar(80)

   declare @deliverDate datetime

   declare @curType varchar(3)

   declare @exchgRate decimal(18,8)

   declare @ordStatus int

   declare @disData int

   declare @endStatus int

   declare @docFrom char(1)

   declare @linkMan varchar(20)

   declare @cellPhone varchar(50)

   declare @listSum decimal(18,8)

            declare @orderCatgy char(2)

            declare @usercomp varchar(30)

            declare @rundept varchar(15)

            declare @runemp varchar(15)       

   --取数据

   select @orderId=orderId,@recordDate=recordDate,@recordMan=recordman,@orderComp=ordercomp,@orderDept=orderDept,

   @oCode=ocode,@orderSum=orderSum,@orderType=orderType,@salePre=salepre,@recComp=reccomp,@recAddr=recAddr,@appMan

   =appMan,@appDate=appdate,@deliverAddr=deliverAddr,@deliverDate=deliverDate,@curType=curType,@exchgRate=exchgRate,

   @endStatus=ordStatus,@disData=disdata,@endStatus=endStatus,@docFrom=docFrom,@linkMan=linkman,@cellPhone=cellphone,

   @listSum=listSum,@orderCatgy=order_category from #TempCrmSenderTable where rowNumber = @rowNo

            select @rundept = deptno,@runemp=empno from fg_customfile where compno=@ordercomp

            if (@orderCatgy='02')

                begin

                   set @orderNo= 'F_SN'+right(@orderId,11)

                   set @usercomp = @orderComp

                   update ec_ordersdtl set orderno=@orderNo where orderid=@orderId

                end

            else

               begin

                   set @orderNo = @orderId

               end

            if (@orderType='01')

               begin

                  set @orderType ='09'

               end 

             else if (@orderType='03')

               begin

                 set @orderType='01'

               end        

            insert into ec_ordersmst(orderid,orderno,recorddate,recordman,ordercomp,orderdept,ocode,ordersum,ordertype,salepre,

            reccomp,recaddr,appman,appdate,deliveraddr,deliverdate,curtype,exchgrate,ordstatus,disdata,endstatus,docfrom,fordhwsum,

            reclinkman,rectelephone,statcomp,user_001,paycomp,disctype,sendflg,appflg,endopt,priceget,sendwh,user_002,verdtm,rundept,

            runemp,usercomp,ordhwsum,orderdisc,discrate,fordersum,forderdisc,purseflg,fnotaxordsum,fnotaxordhwsum,ftaxsum,fnotaxdissum,

            notaxordsum,notaxordhwsum,taxsum,notaxdissum)

            values(@orderId,@orderNo,@recordDate,@recordMan,@orderComp,@orderDept,@oCode,@orderSum,@orderType,@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,@usercomp,@listsum,@listsum-@ordersum,@ordersum/@listsum,

            @orderSum,@listsum-@ordersum,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(@ordersum/1.17*0.17,2),round(@listsum-@ordersum/1.17*0.17-@ordersum/1.17-@ordersum/1.17*0.17,2))--

                        --插入日志表

            insert into crm_to_i6_log (dbtbname,orderid,insertdate) values('ec_ordersmst',@orderId,getdate())

            insert into ec_carryinfo(orderid,lineid,orderno) values( @orderid,1,@orderno)

                       --插入日志表

            insert into crm_to_i6_log (dbtbname,orderid,insertdate) values('ec_carryinfo',@orderId,getdate())

   -- update crm feedback=1 exec I6GetPurOrderFromCRM select * from fg_customfile

   declare @updateC varchar(300)

   declare @selectC varchar(200)

            print @orderId

   set @selectC = '''select * from ERPOrderInfoSync where orderId = '''''+@orderId+''''''''

   set @updateC = 'update openquery(TOFANSKICRM, '+@selectC+') set feedback = 1;'

   -- select * from  openquery(TOFANSKICRM,'select * from ERPOrderInfoSync' ) exec I6GetPurOrderFromCRM

            exec(@updateC)

   set @rowNo=@rowNo+1

      end

 drop table #TempCrmSenderTable

end

摘自 yuefengyuan的专栏