sql计划任务+游标+事务实现不同数据库表之间数据采集

--不显示计数信息
SET NOCOUNT ON
DECLARE @CFREE2 varchar(10),@PNO varchar(10),@PLINE VARCHAR(2),@PTZS VARCHAR(5),@CFREE1 VARCHAR(20),@CFREE3 VARCHAR(20),
        @QTY VARCHAR(5),@ICHANGERATE VARCHAR(5),@CREATEDATE DATE,@CREATETIME DATETIME,@CCOMUNITCODE VARCHAR(10),@CINVCODE VARCHAR(30),
        @CCOMADDUNITCODE VARCHAR(10),@CSALEUNITCODE VARCHAR(10),@PCODE VARCHAR(30),@WJID INT,@ID INT      
        SET @WJID=(SELECT WJID FROM [UFDATA_005_2014].[DBO].[WS_JHRW]);
--声明游标
DECLARE CRMPSContact_cursor CURSOR FOR
SELECT PCODE,SUBSTRING(PBATCH,2,CHARINDEX('-',PBATCH)-4) PNO,RIGHT(LEFT(PBATCH,CHARINDEX('-',PBATCH)-1),2) PLINK,
PTZS,PCLASS CFREE1,PBATCH CFREE3,PNWEIGHT QTY,PDATE CREATEDATE,PRINTTIME CREATETIME,ID
FROM [10.180.20.20].[JINGJIU_DB].[DBO].[t_PrinterHistory]
WHERE ID>@WJID
--打开游标
OPEN CRMPSContact_cursor
FETCH NEXT FROM CRMPSContact_cursor
INTO @PCODE,@PNO,@PLINE,@PTZS,@CFREE1,@CFREE3,@QTY,@CREATEDATE,@CREATETIME,@ID
WHILE @@FETCH_STATUS = 0
BEGIN      
 IF EXISTS (SELECT * FROM [UFDATA_005_2014].[DBO].[INVENTORY] WHERE LEFT(CINVCODE,1)=1 and REPLACE(REPLACE(CINVSTD,'dtex/',''),'f','')=@PNO)
 BEGIN
--取得要用的变量
 SET @CINVCODE=(SELECT CINVCODE FROM [UFDATA_005_2014].[DBO].[INVENTORY] WHERE LEFT(CINVCODE,1)=1 and REPLACE(REPLACE(CINVSTD,'dtex/',''),'f','')=@PNO);
 SET @CCOMUNITCODE=(SELECT CCOMUNITCODE FROM [UFDATA_005_2014].[DBO].[INVENTORY] WHERE LEFT(CINVCODE,1)=1 and REPLACE(REPLACE(CINVSTD,'dtex/',''),'f','')=@PNO);
 SET @CCOMADDUNITCODE=(SELECT CASSCOMUNITCODE FROM [UFDATA_005_2014].[DBO].[INVENTORY] WHERE LEFT(CINVCODE,1)=1 and REPLACE(REPLACE(CINVSTD,'dtex/',''),'f','')=@PNO);
 SET @CSALEUNITCODE=(SELECT CSACOMUNITCODE FROM [UFDATA_005_2014].[DBO].[INVENTORY] WHERE LEFT(CINVCODE,1)=1 and REPLACE(REPLACE(CINVSTD,'dtex/',''),'f','')=@PNO);
 SET @CFREE2 =
        CASE
        WHEN @PTZS = '99' THEN 'A9965'
        WHEN @PTZS = '81' THEN 'A8180'
        WHEN @PTZS = '04'
        THEN
        CASE WHEN @PLINE <7 THEN 'B0465'
        ELSE 'B0480'
        END
        WHEN @PTZS > 99
        THEN
        CASE WHEN @PLINE <7 THEN 'A0065'
        ELSE 'A0080'
        END
        ELSE '其他'
        END;
--开始事务
      BEGIN TRAN
      INSERT INTO [UFDATA_005_2014].[DBO].[HY_BARCODEMAIN]
      ([BarCode]
      ,[BarCodeRule]
      ,[cInvCode]
      ,[cVenCode]
      ,[cWhCode]
      ,[cPosCode]
      ,[iInvSaleCost]
      ,[dMdate]
      ,[dVdate]
      ,[iMassdate]
      ,[cMassUnit]
      ,[cChkItemCode]
      ,[cOther]
      ,[cHoldItem]
      ,[cSHoldItem]
      ,[cSilvItem]
      ,[cFree1]
      ,[cFree2]
      ,[cFree3]
      ,[cFree4]
      ,[cFree5]
      ,[cFree6]
      ,[cFree7]
      ,[cFree8]
      ,[cFree9]
      ,[cFree10]
      ,[qty]
      ,[iNum]
      ,[pLot]
      ,[ichangerate]
      ,[cInvSN]
      ,[cDefine1]
      ,[cDefine2]
      ,[cDefine3]
      ,[cDefine4]
      ,[cDefine5]
      ,[cDefine6]
      ,[cDefine7]
      ,[cDefine8]
      ,[cDefine9]
      ,[cDefine10]
      ,[cDefine11]
      ,[cDefine12]
      ,[cDefine13]
      ,[cDefine14]
      ,[cDefine15]
      ,[cDefine16]
      ,[cDefine22]
      ,[cDefine23]
      ,[cDefine24]
      ,[cDefine25]
      ,[cDefine26]
      ,[cDefine27]
      ,[cDefine28]
      ,[cDefine29]
      ,[cDefine30]
      ,[cDefine31]
      ,[cDefine32]
      ,[cDefine33]
      ,[cDefine34]
      ,[cDefine35]
      ,[cDefine36]
      ,[cDefine37]
      ,[CreateDate]
      ,[CreateTime]
      ,[dBusDate]
      ,[cBarcodeDefine1]
      ,[cBarcodeDefine2]
      ,[cBarcodeDefine3]
      ,[cBarcodeDefine4]
      ,[cBarcodeDefine5]
      ,[cBarcodeDefine6]
      ,[cBarcodeDefine7]
      ,[cBarcodeDefine8]
      ,[cBarcodeDefine9]
      ,[cBarcodeDefine10]
      ,[cComUnitCode]
      ,[cComAddUnitCode]
      ,[cSrcCode]
      ,[cSrcVouchType]
      ,[cSrcSubID]
      ,[cBarMainID]
      ,[cBarMainAutoID]
      ,[cMaker]
      ,[cGuid]
      ,[cLabelCode]
      ,[supBarCode]
      ,[iPrtCount]
      ,[iBarCodeState]
      ,[bExpSub]
      ,[cNoUseMaker]
      ,[dNoUseTime]
      ,[bUseLs]
      ,[cInvBarCode]
      ,[cinvcBarCode]
      ,[cWhBarCode]
      ,[cPosBarCode]
      ,[cVenBarCode]
      ,[cSaleUnitCode]
      ,[iSaleQty]
      ,[iSalePrice]
      ,[iExpiratDateCalcu]
      ,[cExpirationdate]
      ,[dExpirationdate]
      ,[cBatchProperty1]
      ,[cBatchProperty2]
      ,[cBatchProperty3]
      ,[cBatchProperty4]
      ,[cBatchProperty5]
      ,[cBatchProperty6]
      ,[cBatchProperty7]
      ,[cBatchProperty8]
      ,[cBatchProperty9]
      ,[cBatchProperty10]
      ,[irowno]
      ,[iRelAutoid]
 )
 SELECT TOP 1
       @PCODE BARCODE
      ,BARCODERULE
      ,@CINVCODE [CINVCODE]
      ,[cVenCode]
      ,[cWhCode]
      ,[cPosCode]
      ,[iInvSaleCost]
      ,[dMdate]
      ,[dVdate]
      ,[iMassdate]
      ,[cMassUnit]
      ,[cChkItemCode]
      ,[cOther]
      ,[cHoldItem]
      ,[cSHoldItem]
      ,right('0000000000'+convert(nvarchar(10),convert(int,[cSilvItem])+1),9) cSilvItem
      ,@CFREE1 cFree1
      ,@CFREE2 cFree2
      ,@CFREE3 cFree3
      ,[cFree4]
      ,[cFree5]
      ,[cFree6]
      ,[cFree7]
      ,[cFree8]
      ,[cFree9]
      ,[cFree10]
      ,@QTY qty
      ,[iNum]
      ,[pLot]
      ,@QTY ichangerate
      ,[cInvSN]
      ,[cDefine1]
      ,[cDefine2]
      ,[cDefine3]
      ,[cDefine4]
      ,[cDefine5]
      ,[cDefine6]
      ,[cDefine7]
      ,[cDefine8]
      ,[cDefine9]
      ,[cDefine10]
      ,[cDefine11]
      ,[cDefine12]
      ,[cDefine13]
      ,[cDefine14]
      ,[cDefine15]
      ,[cDefine16]
      ,[cDefine22]
      ,[cDefine23]
      ,[cDefine24]
      ,[cDefine25]
      ,[cDefine26]
      ,[cDefine27]
      ,[cDefine28]
      ,[cDefine29]
      ,[cDefine30]
      ,[cDefine31]
      ,[cDefine32]
      ,[cDefine33]
      ,[cDefine34]
      ,[cDefine35]
      ,[cDefine36]
      ,[cDefine37]
      ,@CREATEDATE [CreateDate]
      ,@CREATETIME [CreateTime]
      ,@CREATEDATE [dBusDate]
      ,[cBarcodeDefine1]
      ,[cBarcodeDefine2]
      ,[cBarcodeDefine3]
      ,[cBarcodeDefine4]
      ,[cBarcodeDefine5]
      ,[cBarcodeDefine6]
      ,[cBarcodeDefine7]
      ,[cBarcodeDefine8]
      ,[cBarcodeDefine9]
      ,[cBarcodeDefine10]
      ,@CCOMUNITCODE [cComUnitCode]
      ,@CCOMADDUNITCODE [cComAddUnitCode]
      ,[cSrcCode]
      ,[cSrcVouchType]
      ,[cSrcSubID]
      ,[cBarMainID]
      ,[cBarMainAutoID]
      ,[cMaker]
      ,[cGuid]
      ,[cLabelCode]
      ,[supBarCode]
      ,'0' [iPrtCount]
      ,[iBarCodeState]
      ,[bExpSub]
      ,[cNoUseMaker]
      ,[dNoUseTime]
      ,[bUseLs]
      ,[cInvBarCode]
      ,[cinvcBarCode]
      ,[cWhBarCode]
      ,[cPosBarCode]
      ,[cVenBarCode]
      ,@CSALEUNITCODE [cSaleUnitCode]
      ,[iSaleQty]
      ,[iSalePrice]
      ,[iExpiratDateCalcu]
      ,[cExpirationdate]
      ,[dExpirationdate]
      ,[cBatchProperty1]
      ,[cBatchProperty2]
      ,[cBatchProperty3]
      ,[cBatchProperty4]
      ,[cBatchProperty5]
      ,[cBatchProperty6]
      ,[cBatchProperty7]
      ,[cBatchProperty8]
      ,[cBatchProperty9]
      ,[cBatchProperty10]
      ,[irowno]
      ,[iRelAutoid]
      
    FROM [UFDATA_005_2014].[DBO].[HY_BARCODEMAIN] order by [cSilvItem] DESC
    
    UPDATE [UFDATA_005_2014].[DBO].[WS_JHRW] SET WJID=@ID
--提交所有变更
    COMMIT TRAN
--执行错误回滚
   if @@error!=0
   begin
     rollback tran
     return
   end
END
FETCH NEXT FROM CRMPSContact_cursor INTO @PCODE,@PNO,@PLINE,@PTZS,@CFREE1,@CFREE3,@QTY,@CREATEDATE,@CREATETIME,@ID  
END
--关闭游标
CLOSE CRMPSContact_cursor
--释放游标
DEALLOCATE CRMPSContact_cursor
--恢复设置
SET NOCOUNT OFF

GO