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,

         &nbs