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

最新推荐文章于 2022-04-04 17:59:09 发布

置顶

岳丰源

最新推荐文章于 2022-04-04 17:59:09 发布

阅读量2.6k

点赞数 1

分类专栏：数据库应用文章标签：数据库 sql server 存储 insert crm table

本文链接：https://blog.csdn.net/yuefengyuan/article/details/7033613

版权

这是一个SQL Server存储过程示例，用于跨数据库从CRM系统抓取未反馈的数据，并将数据插入到目标数据库的ec_ordersdtl和ec_ordersmst表中，同时更新CRM系统的反馈状态。

摘要由CSDN通过智能技术生成

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,it