alter procedure CCCMoveSalesBufferDataToArchive as begin declare @findocs2Process TABLE (findoc int); declare @finJustDel TABLE (findoc int); declare @mdocJustDel TABLE (findoc int); declare @mtlJustDel TABLE (findoc int); declare @frowsToMove TABLE (findoc int); declare @finJustDelCount int; declare @mdocJustDelCount int; declare @mtlJustDelCount int; declare @finp int; declare @tmp_fin int; print 'Deleting payment findocs'; delete from findoc where trndate < dateadd(day, -3, GETDATE()) and SOSOURCE = 1381; -- Update MTRL table print 'DELETING ROWS ON ArchiveSoft1DB.dbo.MTRL'; delete ArchiveSoft1DB.dbo.MTRL; print 'INSERTING NEW ROWS ON ArchiveSoft1DB.dbo.MTRL'; set identity_insert ArchiveSoft1DB.dbo.[MTRL] on INSERT INTO ArchiveSoft1DB.[dbo].[MTRL]( [COMPANY],[SODTYPE],[LOCKID],[MTRL],[CODE],[NAME],[CODE1],[CODE2],[RELITEM],[NAME1],[ISACTIVE],[MTRTYPE],[MTRTYPE1],[MTRACN],[MTRCATEGORY],[VAT],[MTRUNIT1],[MTRUNIT2],[MTRUNIT3],[MTRUNIT4],[MU21],[MU31],[MU41],[MU12MODE],[MU13MODE],[MU14MODE],[MTRGROUP],[MTRMANFCTR],[COUNTRY],[BUSUNITS],[COSTCNTR],[MTRDUTY],[MTRPCATEGORY],[MTRMODEL],[MTRMARK],[CHK],[COMMISION],[MTRSEASON],[SOCURRENCY],[WARNING],[KEPYO],[MTRDUN],[INTRASTAT],[MUMD],[VOLUME],[WEIGHT],[WEBPAGE],[MTRPLACE],[PRICEW],[PRICER],[MARKUPW],[MARKUPR],[REMAINMODE],[ACNMSK],[ACNMSK1],[ACNMSK2],[ACNMSK3],[ACNMSK4],[ACNMSK5],[PRICEW01],[PRICEW02],[PRICEW03],[PRICEW04],[PRICEW05],[PRICEW06],[PRICEW07],[PRICEW08],[PRICEW09],[PRICEW10],[PRICEW11],[PRICEW12],[PRICER01],[PRICER02],[PRICER03],[PRICER04],[PRICER05],[PRICER06],[PRICER07],[PRICER08],[PRICER09],[PRICER10],[PRICER11],[PRICER12],[DIM1],[DIM2],[DIM3],[DIMMD],[DIMMTRUNIT],[SALQTY],[PURQTY],[ITEQTY],[FROMVAL],[SODISCOUNT],[MAXPRCDISC],[REPLPUR],[REPLSAL],[REPLITE],[SPCS],[AUTOUPDPUR],[AUTOUPDSAL],[AUTOUPDITE],[PRINTPURMD],[PRINTSALMD],[PRINTITEMD],[UNIQSUB],[LOTCODEMASK],[MTRLOTUSE],[MTRSNUSE],[SNCODEMSK],[ISTOTSRVCARD],[MTRTHIRD],[USESTBIN],[MTRONORDER],[TURNOVR],[SETITEM],[REPLPRICE],[REPLEXP],[MTRRPLCODE],[MTRCUS],[MTRSUP],[MTRSUPBRANCH],[REMAINLIMMIN],[REMAINLIMMAX],[REORDERLEVEL],[EXPN1],[EXPN2],[EXPN3],[EXPVAL1],[EXPVAL2],[EXPVAL3],[LISOURCETYPE],[SOCLMNS],[PRDLEVEL],[REMARKS],[CDIMCATEG1],[CDIM1],[CDIMCATEG2],[CDIM2],[CDIMCATEG3],[CDIM3],[CDIMNUSE1],[CDIMNUSE2],[CDIMNUSE3],[MTRDEFWHS],[VISITUNIT],[VISITNUM],[VATMTRL],[ACNMSKS],[ACNMSKX],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[SXMSKK],[CDIMLEVEL],[ACNMSKSXD],[CRDCARDMODE],[MTRGASTYPE],[EXPN4],[EXPN5],[EXPVAL4],[EXPVAL5],[CHKMAXPRCDISC],[SOCLMNS1],[MTRPRJCBLD],[SOEXPTYPE],[ACNMSK6],[MINPRCMK],[MTRPACK],[CCCTerraRelItem],[calconcredit],[WEBNAME],[WEBVIEW],[CCCTerraOFFERSTARTDATE],[CCCTerraOFFERENDDATE],[CCCTerraOFFERPRICE],[ACNMSK9],[GUARTIME],[GSISVALUE],[CCCITEMDEPARTMENT],[CCCITEMWEIGHTED],[CCCITEMEXLUDEDISC],[CCCXATOPOS],[CCCPLU],[CCCPARELK],[CCCQPARELK],[CCCMMP],[CCCPMM],[CCCPMP],[CCCMKOD],[CCCMTRGRSUP],[CCCMTRSUP],[APVCODE],[SODISCOUNT1],[SODISCOUNT2],[SPCITESRV],[SOPAYVALUE],[MTRLA],[MTRLP]) SELECT [COMPANY],[SODTYPE],[LOCKID],[MTRL],[CODE],[NAME],[CODE1],[CODE2],[RELITEM],[NAME1],[ISACTIVE],[MTRTYPE],[MTRTYPE1],[MTRACN],[MTRCATEGORY],[VAT],[MTRUNIT1],[MTRUNIT2],[MTRUNIT3],[MTRUNIT4],[MU21],[MU31],[MU41],[MU12MODE],[MU13MODE],[MU14MODE],[MTRGROUP],[MTRMANFCTR],[COUNTRY],[BUSUNITS],[COSTCNTR],[MTRDUTY],[MTRPCATEGORY],[MTRMODEL],[MTRMARK],[CHK],[COMMISION],[MTRSEASON],[SOCURRENCY],[WARNING],[KEPYO],[MTRDUN],[INTRASTAT],[MUMD],[VOLUME],[WEIGHT],[WEBPAGE],[MTRPLACE],[PRICEW],[PRICER],[MARKUPW],[MARKUPR],[REMAINMODE],[ACNMSK],[ACNMSK1],[ACNMSK2],[ACNMSK3],[ACNMSK4],[ACNMSK5],[PRICEW01],[PRICEW02],[PRICEW03],[PRICEW04],[PRICEW05],[PRICEW06],[PRICEW07],[PRICEW08],[PRICEW09],[PRICEW10],[PRICEW11],[PRICEW12],[PRICER01],[PRICER02],[PRICER03],[PRICER04],[PRICER05],[PRICER06],[PRICER07],[PRICER08],[PRICER09],[PRICER10],[PRICER11],[PRICER12],[DIM1],[DIM2],[DIM3],[DIMMD],[DIMMTRUNIT],[SALQTY],[PURQTY],[ITEQTY],[FROMVAL],[SODISCOUNT],[MAXPRCDISC],[REPLPUR],[REPLSAL],[REPLITE],[SPCS],[AUTOUPDPUR],[AUTOUPDSAL],[AUTOUPDITE],[PRINTPURMD],[PRINTSALMD],[PRINTITEMD],[UNIQSUB],[LOTCODEMASK],[MTRLOTUSE],[MTRSNUSE],[SNCODEMSK],[ISTOTSRVCARD],[MTRTHIRD],[USESTBIN],[MTRONORDER],[TURNOVR],[SETITEM],[REPLPRICE],[REPLEXP],[MTRRPLCODE],[MTRCUS],[MTRSUP],[MTRSUPBRANCH],[REMAINLIMMIN],[REMAINLIMMAX],[REORDERLEVEL],[EXPN1],[EXPN2],[EXPN3],[EXPVAL1],[EXPVAL2],[EXPVAL3],[LISOURCETYPE],[SOCLMNS],[PRDLEVEL],[REMARKS],[CDIMCATEG1],[CDIM1],[CDIMCATEG2],[CDIM2],[CDIMCATEG3],[CDIM3],[CDIMNUSE1],[CDIMNUSE2],[CDIMNUSE3],[MTRDEFWHS],[VISITUNIT],[VISITNUM],[VATMTRL],[ACNMSKS],[ACNMSKX],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[SXMSKK],[CDIMLEVEL],[ACNMSKSXD],[CRDCARDMODE],[MTRGASTYPE],[EXPN4],[EXPN5],[EXPVAL4],[EXPVAL5],[CHKMAXPRCDISC],[SOCLMNS1],[MTRPRJCBLD],[SOEXPTYPE],[ACNMSK6],[MINPRCMK],[MTRPACK],[CCCTerraRelItem],[calconcredit],[WEBNAME],[WEBVIEW],[CCCTerraOFFERSTARTDATE],[CCCTerraOFFERENDDATE],[CCCTerraOFFERPRICE],[ACNMSK9],[GUARTIME],[GSISVALUE],[CCCITEMDEPARTMENT],[CCCITEMWEIGHTED],[CCCITEMEXLUDEDISC],[CCCXATOPOS],[CCCPLU],[CCCPARELK],[CCCQPARELK],[CCCMMP],[CCCPMM],[CCCPMP],[CCCMKOD],[CCCMTRGRSUP],[CCCMTRSUP],[APVCODE],[SODISCOUNT1],[SODISCOUNT2],[SPCITESRV],[SOPAYVALUE],[MTRLA],[MTRLP] FROM [dbo].[MTRL] where sodtype = 51; set identity_insert ArchiveSoft1DB.dbo.[MTRL] off -- Update TRDR table print 'DELETING ROWS ON ArchiveSoft1DB.dbo.TRDR'; delete ArchiveSoft1DB.dbo.TRDR; print 'INSERTING NEW ROWS ON ArchiveSoft1DB.dbo.TRDR'; set identity_insert ArchiveSoft1DB.dbo.[TRDR] on INSERT INTO ArchiveSoft1DB.[dbo].[TRDR]( [COMPANY],[LOCKID],[SODTYPE],[TRDR],[CODE],[NAME],[AFM],[ISACTIVE],[ISPROSP],[COUNTRY],[SOCURRENCY],[BRANCH],[ADDRESS],[ZIP],[DISTRICT],[CITY],[AREAS],[PHONE01],[PHONE02],[FAX],[JOBTYPE],[JOBTYPETRD],[TRDGROUP],[WEBPAGE],[EMAIL],[TRDCATEGORY],[TRDBUSINESS],[SHIPMENT],[PAYMENT],[PRCCATEGORY],[BUSUNITS],[PRIORITY],[PRCPOLICY],[DSCPOLICY],[SOCARRIER],[TRUCKS],[ROUTING],[SALESMAN],[COLLECTOR],[CODE1],[IRSDATA],[VATSTS],[KEPYOSTS],[CMPMODE],[ACNMSK],[OPITMODE],[CRCONTROL],[CRDLINES1],[CRDLIMIT1],[CRDLINES2],[CRDLIMIT2],[DISCOUNT],[PAYHFROM],[PAYHTO],[PAYEVERY],[PAYDAY],[PAYWEEK],[WARNING],[EXPGROUP],[BANK],[BANKBRANCH],[BANKACCNUMBER],[BANKRATE1],[BANKRATE2],[ACNMSK1],[BANCTYPE],[MINBAL],[MAXBAL],[IBAN],[SPCCHAR],[BGBULSTAT],[REMARKS],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[CUSDATE],[CUSUSER],[ISCONTRACTOR],[SPCOST],[EFKFLAG],[DISTRICT1],[TRDTYPE1],[CONWHOUSE],[OLDEH],[OLAFM],[COMMISION],[CRDLINES3],[CRDLIMIT3],[CRDLINES4],[CRDLIMIT4],[CRDLINES5],[CRDLIMIT5],[MAXPRCDISC],[CHKMAXPRCDISC],[GASCUSTYPE],[kepyomd],[ISCMPT],[codelinemd],[ISVALCREDIT],[rowguid],[opitfindoc],[SOSCORE],[PRJCS],[SWIFT],[VISITEVERY],[CHKAFM],[EMAILACC],[RECEIPTCARD],[ECOLLABORATION],[VATPROVISIONS],[WASTETYPE],[S1INVMD],[GSISMD],[SOPAYCODE],[NUMCG],[INPAYVAT]) SELECT [COMPANY],[LOCKID],[SODTYPE],[TRDR],[CODE],[NAME],[AFM],[ISACTIVE],[ISPROSP],[COUNTRY],[SOCURRENCY],[BRANCH],[ADDRESS],[ZIP],[DISTRICT],[CITY],[AREAS],[PHONE01],[PHONE02],[FAX],[JOBTYPE],[JOBTYPETRD],[TRDGROUP],[WEBPAGE],[EMAIL],[TRDCATEGORY],[TRDBUSINESS],[SHIPMENT],[PAYMENT],[PRCCATEGORY],[BUSUNITS],[PRIORITY],[PRCPOLICY],[DSCPOLICY],[SOCARRIER],[TRUCKS],[ROUTING],[SALESMAN],[COLLECTOR],[CODE1],[IRSDATA],[VATSTS],[KEPYOSTS],[CMPMODE],[ACNMSK],[OPITMODE],[CRCONTROL],[CRDLINES1],[CRDLIMIT1],[CRDLINES2],[CRDLIMIT2],[DISCOUNT],[PAYHFROM],[PAYHTO],[PAYEVERY],[PAYDAY],[PAYWEEK],[WARNING],[EXPGROUP],[BANK],[BANKBRANCH],[BANKACCNUMBER],[BANKRATE1],[BANKRATE2],[ACNMSK1],[BANCTYPE],[MINBAL],[MAXBAL],[IBAN],[SPCCHAR],[BGBULSTAT],[REMARKS],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[CUSDATE],[CUSUSER],[ISCONTRACTOR],[SPCOST],[EFKFLAG],[DISTRICT1],[TRDTYPE1],[CONWHOUSE],[OLDEH],[OLAFM],[COMMISION],[CRDLINES3],[CRDLIMIT3],[CRDLINES4],[CRDLIMIT4],[CRDLINES5],[CRDLIMIT5],[MAXPRCDISC],[CHKMAXPRCDISC],[GASCUSTYPE],[kepyomd],[ISCMPT],[codelinemd],[ISVALCREDIT],[rowguid],[opitfindoc],[SOSCORE],[PRJCS],[SWIFT],[VISITEVERY],[CHKAFM],[EMAILACC],[RECEIPTCARD],[ECOLLABORATION],[VATPROVISIONS],[WASTETYPE],[S1INVMD],[GSISMD],[SOPAYCODE],[NUMCG],[INPAYVAT] FROM [dbo].[TRDR] where sodtype = 13 set identity_insert ArchiveSoft1DB.dbo.[TRDR] off -- Fetch findocs to process insert into @findocs2Process select top 10000 findoc from findoc where CCCTerraHasExported = 1 and trndate < dateadd(week, -1, GETDATE()) order by TRNDATE; -- Fetch findocs that are already in archive insert into @finJustDel select finS.findoc from @findocs2Process finS inner join ArchiveSoft1DB.dbo.FINDOC finA on finA.FINDOC = finS.FINDOC; -- Fetch mtrdocs that are already in archive insert into @mdocJustDel select finP.findoc from @findocs2Process finP inner join ArchiveSoft1DB.dbo.MTRDOC mdocA on mdocA.FINDOC = finP.FINDOC; -- Fetch mtrlines that are already in archive insert into @mtlJustDel select finP.findoc from @findocs2Process finP inner join ArchiveSoft1DB.dbo.MTRLINES mtlA on mtlA.FINDOC = finP.FINDOC; -- Finally, fetch rows to move insert into @frowsToMove select findoc from @findocs2Process where findoc not in (select findoc from @finJustDel); -- DELETE already stored rows to ArchiveSoft1DB from Salesbuffer set @finJustDelCount = (select count(*) from @finJustDel); set @mdocJustDelCount = (select count(*) from @mdocJustDel); set @mtlJustDelCount = (select count(*) from @mtlJustDel); if @mdocJustDelCount > 0 begin print 'DELETING EXISTINGS ROWS On SalesBufferDB for MTRDOC'; delete from MTRDOC where FINDOC in (select FINDOC from @mdocJustDel); end; if @mtlJustDelCount > 0 begin print 'DELETING EXISTINGS ROWS On SalesBufferDB for MTRLINES'; delete from MTRLINES where FINDOC in (select FINDOC from @mtlJustDel); end; if @finJustDelCount > 0 begin print 'DELETING EXISTINGS ROWS On SalesBufferDB for FINDOC'; delete from Findoc where FINDOC in (select FINDOC from @finJustDel); end; -- Insert findoc set identity_insert ArchiveSoft1DB.dbo.Findoc on INSERT INTO ArchiveSoft1DB.dbo.[FINDOC]( [COMPANY],[LOCKID],[FINDOC],[SOSOURCE],[SOREDIR],[TRNDATE],[FISCPRD],[PERIOD],[SERIES],[SERIESNUM],[FPRMS],[TFPRMS],[FINCODE],[BRANCH],[SODTYPE],[TRDR],[TRDBRANCH],[VATSTS],[SOCURRENCY],[TRDRRATE],[LRATE],[FINDOCS],[MTRLINESS],[FINDOCSRV],[FINDOCPAY],[FINDOCSX],[ORIGIN],[GLUPD],[SXUPD],[ACNEDIT],[PRDCOST],[PROCDEF],[COMMENTS],[COMMENTS1],[ISCANCEL],[ISPRINT],[ISREADONLY],[APPRVDATE],[APPRVUSER],[APPRV],[CONVMODE],[FULLYTRANSF],[FINSTATES],[SHIPKIND],[SHIPMENT],[BUSUNITS],[PAYMENT],[CRCONTROL],[PRIORITY],[INST],[PRJC],[SALESMAN],[COLLECTOR],[RSRC],[SOCASH],[FOLDER],[DEPART],[ACNMSK],[TRDRS],[TRDBRANCHS],[LTYPE1],[LTYPE2],[LTYPE3],[LTYPE4],[SOTIME],[TURNOVR],[TTURNOVR],[LTURNOVR],[VATAMNT],[TVATAMNT],[LVATAMNT],[EXPN],[TEXPN],[LEXPN],[DISC1PRC],[DISC1VAL],[TDISC1VAL],[LDISC1VAL],[DISC2PRC],[DISC2VAL],[TDISC2VAL],[LDISC2VAL],[NETAMNT],[TNETAMNT],[LNETAMNT],[SUMAMNT],[SUMTAMNT],[SUMLAMNT],[FXDIFFVAL],[KEPYOQT],[LKEPYOVAL],[CHANGEVAL],[PRCRULEDATA],[INTDATE],[INTRATE],[INTVAL],[INTEXPN],[INTVAT],[INTFDOCTYPE],[INTSHIPMENT],[ISTRIG],[BGSUBJECT],[BGOTHRVAT],[BGPARTVAT],[REMARKS],[VARCHAR01],[VARCHAR02],[NUM01],[NUM02],[DATE01],[DATE02],[INT01],[INT02],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[INSDATEN],[PRCPOLICY],[bgdocdate],[KEPYOHANDMD],[GASCUSTYPE],[CASHDEVICE],[BGINTSTATPROC],[BGINTDELIVERY],[BGINTDISTRICT],[KEPYOMD],[CCCIMPPOINTS],[CCCCARD],[CCCTerraHasExported],[CCCTerraRId],[CCCTerraFindocExistanceCheck],[cntr],[SOCURRENCYREF],[NUM03],[NUM04],[BOOL01],[BOOL02],[UFTBL01],[UFTBL02],[prjcstage],[bgdocdate1],[socashcls],[FINDOCG],[EXDIFFPPERIOD],[PRJLINES],[VATPROVISIONS],[CARDPOINTS],[NEGCARDPOINTS],[BONUSCARD],[GSISMD],[GSISQTY],[GSISNET],[GSISVAT],[GSISPACKAGES],[GSISFLG],[WASTETYPE],[SOPAYCODE],[COSTCNTR],[INPAYVAT]) SELECT [COMPANY],[LOCKID],[FINDOC],[SOSOURCE],[SOREDIR],[TRNDATE],[FISCPRD],[PERIOD],[SERIES],[SERIESNUM],[FPRMS],[TFPRMS],[FINCODE],[BRANCH],[SODTYPE],[TRDR],[TRDBRANCH],[VATSTS],[SOCURRENCY],[TRDRRATE],[LRATE],[FINDOCS],[MTRLINESS],[FINDOCSRV],[FINDOCPAY],[FINDOCSX],[ORIGIN],[GLUPD],[SXUPD],[ACNEDIT],[PRDCOST],[PROCDEF],[COMMENTS],[COMMENTS1],[ISCANCEL],[ISPRINT],[ISREADONLY],[APPRVDATE],[APPRVUSER],[APPRV],[CONVMODE],[FULLYTRANSF],[FINSTATES],[SHIPKIND],[SHIPMENT],[BUSUNITS],[PAYMENT],[CRCONTROL],[PRIORITY],[INST],[PRJC],[SALESMAN],[COLLECTOR],[RSRC],[SOCASH],[FOLDER],[DEPART],[ACNMSK],[TRDRS],[TRDBRANCHS],[LTYPE1],[LTYPE2],[LTYPE3],[LTYPE4],[SOTIME],[TURNOVR],[TTURNOVR],[LTURNOVR],[VATAMNT],[TVATAMNT],[LVATAMNT],[EXPN],[TEXPN],[LEXPN],[DISC1PRC],[DISC1VAL],[TDISC1VAL],[LDISC1VAL],[DISC2PRC],[DISC2VAL],[TDISC2VAL],[LDISC2VAL],[NETAMNT],[TNETAMNT],[LNETAMNT],[SUMAMNT],[SUMTAMNT],[SUMLAMNT],[FXDIFFVAL],[KEPYOQT],[LKEPYOVAL],[CHANGEVAL],[PRCRULEDATA],[INTDATE],[INTRATE],[INTVAL],[INTEXPN],[INTVAT],[INTFDOCTYPE],[INTSHIPMENT],[ISTRIG],[BGSUBJECT],[BGOTHRVAT],[BGPARTVAT],[REMARKS],[VARCHAR01],[VARCHAR02],[NUM01],[NUM02],[DATE01],[DATE02],[INT01],[INT02],[INSDATE],[INSUSER],[UPDDATE],[UPDUSER],[INSDATEN],[PRCPOLICY],[bgdocdate],[KEPYOHANDMD],[GASCUSTYPE],[CASHDEVICE],[BGINTSTATPROC],[BGINTDELIVERY],[BGINTDISTRICT],[KEPYOMD],[CCCIMPPOINTS],[CCCCARD],[CCCTerraHasExported],[CCCTerraRId],[CCCTerraFindocExistanceCheck],[cntr],[SOCURRENCYREF],[NUM03],[NUM04],[BOOL01],[BOOL02],[UFTBL01],[UFTBL02],[prjcstage],[bgdocdate1],[socashcls],[FINDOCG],[EXDIFFPPERIOD],[PRJLINES],[VATPROVISIONS],[CARDPOINTS],[NEGCARDPOINTS],[BONUSCARD],[GSISMD],[GSISQTY],[GSISNET],[GSISVAT],[GSISPACKAGES],[GSISFLG],[WASTETYPE],[SOPAYCODE],[COSTCNTR],[INPAYVAT] FROM [dbo].[FINDOC] where findoc in (select findoc from @frowsToMove); set identity_insert ArchiveSoft1DB.dbo.Findoc off -- Insert MTRDOC INSERT INTO ArchiveSoft1DB.dbo.[MTRDOC] ( [COMPANY],[FINDOC],[BRANCHSEC],[WHOUSESEC],[SOCARRIER],[TRUCKS],[ROUTING],[SHIPPINGADDR],[SHPZIP],[SHPDISTRICT],[SHPCITY],[SHIPDATE],[DELIVDATE],[ORDERTRDR],[ORDERTRDBRANCH],[SHIPTRDR],[SHIPTRDBRANCH],[BILLTRDR],[BILLTRDBRANCH],[MTRL],[SNCODE],[SPCS],[QTY],[QTY1],[QTY2],[QTY1S],[QTY1A],[WASTE],[COSTCOEF],[STAGE],[MTRSTS],[SALESCVAL],[QTY1H],[QTY2H],[PROCDOCS],[SOSIGNB],[BGINTCOUNTRY],[TRUCKSNO],[OLDEH],[OLKIND],[WHOUSE],[procdocs1],[SOSIGNE],[RECEIPTCARD],[FROMDATE],[FINALDATE],[DURATION],[TRDPRSN],[ACTSTATUS],[S1INV],[SORELDOC]) SELECT [COMPANY],[FINDOC],[BRANCHSEC],[WHOUSESEC],[SOCARRIER],[TRUCKS],[ROUTING],[SHIPPINGADDR],[SHPZIP],[SHPDISTRICT],[SHPCITY],[SHIPDATE],[DELIVDATE],[ORDERTRDR],[ORDERTRDBRANCH],[SHIPTRDR],[SHIPTRDBRANCH],[BILLTRDR],[BILLTRDBRANCH],[MTRL],[SNCODE],[SPCS],[QTY],[QTY1],[QTY2],[QTY1S],[QTY1A],[WASTE],[COSTCOEF],[STAGE],[MTRSTS],[SALESCVAL],[QTY1H],[QTY2H],[PROCDOCS],[SOSIGNB],[BGINTCOUNTRY],[TRUCKSNO],[OLDEH],[OLKIND],[WHOUSE],[procdocs1],[SOSIGNE],[RECEIPTCARD],[FROMDATE],[FINALDATE],[DURATION],[TRDPRSN],[ACTSTATUS],[S1INV],[SORELDOC] FROM [dbo].[MTRDOC] where findoc in (select findoc from @frowsToMove); -- Insert Mtrlines INSERT INTO ArchiveSoft1DB.dbo.[MTRLINES]( [COMPANY],[FINDOC],[MTRLINES],[LINENUM],[SODTYPE],[MTRL],[PENDING],[RESTMODE],[SOSOURCE],[SOREDIR],[SCODE],[MTRTYPE],[SOTYPE],[WHOUSE],[WHOUSESEC],[MTRUNIT],[VAT],[PRJC],[SALESMAN],[RSRC],[MTRLOT],[ASSDEPR],[WHOUSEBIN],[WHOUSEBINSEC],[BUSUNITS],[MTRSEASON],[MTRCATEGORY],[SPCS],[QTY],[QTY1],[QTY2],[QTY1COV],[QTY1CANC],[QTY1FCOV],[SHIPDATE],[DELIVDATE],[WEIGHT],[VOLUME],[INTVAL],[INTVAT],[PLSMVAL],[PLSMVAT],[PRICE],[PRICE1],[LINEVAL],[LLINEVAL],[EXPVAL],[LEXPVAL],[DISC1PRC],[DISC2PRC],[DISC3PRC],[DISC1VAL],[LDISC1VAL],[DISC2VAL],[LDISC2VAL],[DISC3VAL],[LDISC3VAL],[NETLINEVAL],[LNETLINEVAL],[VATAMNT],[LVATAMNT],[SALESCVAL],[EFKVAL],[COMMENTS],[COMMENTS1],[DIMENSION1],[DIMENSION2],[DIMENSION3],[TRNLINEVAL],[LTRNLINEVAL],[PRCRULEDATA],[WASTE],[COSTCOEF],[CONVPRC],[STAGE],[STAGELINENUM],[ACNMSK],[ACNMSK1],[FINDOCS],[MTRLINESS],[SOANAL],[BGLEXCISE],[SXMSKK],[SXPERC],[prcpolicy],[autoprddoc],[BGINTCOUNTRY],[BGINTDISTRICT],[MTRPRJCBLD],[FINDOCL],[MTRLINESL],[ACNMSKS],[ACNMSKX],[SOEXPTYPE],[CCCScannedBarcode],[lexpval1],[lexpvatval1],[MTRPACK],[QTYP],[CCCTerraPRICE2],[CCCPOINTS],[CCCCARD1],[CCCCARD2],[INSTALMENTS],[DUTY],[COMMENTS2],[NUM01],[NUM02],[NUM03],[NUM04],[BOOL01],[BOOL02],[DATE01],[DATE02],[UFTBL01],[UFTBL02],[prjcstage],[costcntr],[ITECALCTYPE],[PRJLINES],[GUARTIME],[LVATNOEXM],[SRVDEBITTYPE],[PACKSTATUS],[PACKCODE1],[PACKCODE2]) SELECT [COMPANY],[FINDOC],[MTRLINES],[LINENUM],[SODTYPE],[MTRL],[PENDING],[RESTMODE],[SOSOURCE],[SOREDIR],[SCODE],[MTRTYPE],[SOTYPE],[WHOUSE],[WHOUSESEC],[MTRUNIT],[VAT],[PRJC],[SALESMAN],[RSRC],[MTRLOT],[ASSDEPR],[WHOUSEBIN],[WHOUSEBINSEC],[BUSUNITS],[MTRSEASON],[MTRCATEGORY],[SPCS],[QTY],[QTY1],[QTY2],[QTY1COV],[QTY1CANC],[QTY1FCOV],[SHIPDATE],[DELIVDATE],[WEIGHT],[VOLUME],[INTVAL],[INTVAT],[PLSMVAL],[PLSMVAT],[PRICE],[PRICE1],[LINEVAL],[LLINEVAL],[EXPVAL],[LEXPVAL],[DISC1PRC],[DISC2PRC],[DISC3PRC],[DISC1VAL],[LDISC1VAL],[DISC2VAL],[LDISC2VAL],[DISC3VAL],[LDISC3VAL],[NETLINEVAL],[LNETLINEVAL],[VATAMNT],[LVATAMNT],[SALESCVAL],[EFKVAL],[COMMENTS],[COMMENTS1],[DIMENSION1],[DIMENSION2],[DIMENSION3],[TRNLINEVAL],[LTRNLINEVAL],[PRCRULEDATA],[WASTE],[COSTCOEF],[CONVPRC],[STAGE],[STAGELINENUM],[ACNMSK],[ACNMSK1],[FINDOCS],[MTRLINESS],[SOANAL],[BGLEXCISE],[SXMSKK],[SXPERC],[prcpolicy],[autoprddoc],[BGINTCOUNTRY],[BGINTDISTRICT],[MTRPRJCBLD],[FINDOCL],[MTRLINESL],[ACNMSKS],[ACNMSKX],[SOEXPTYPE],[CCCScannedBarcode],[lexpval1],[lexpvatval1],[MTRPACK],[QTYP],[CCCTerraPRICE2],[CCCPOINTS],[CCCCARD1],[CCCCARD2],[INSTALMENTS],[DUTY],[COMMENTS2],[NUM01],[NUM02],[NUM03],[NUM04],[BOOL01],[BOOL02],[DATE01],[DATE02],[UFTBL01],[UFTBL02],[prjcstage],[costcntr],[ITECALCTYPE],[PRJLINES],[GUARTIME],[LVATNOEXM],[SRVDEBITTYPE],[PACKSTATUS],[PACKCODE1],[PACKCODE2] FROM [dbo].[MTRLINES] where findoc in (select findoc from @frowsToMove); -- delete rows from salesbuffer delete from MTRDOC where findoc in (select findoc from @frowsToMove); delete from MTRLINES where findoc in (select findoc from @frowsToMove); delete from Findoc where findoc in (select findoc from @frowsToMove); -- Shring log file DBCC SHRINKFILE (N'press_Log' , 1) end;