SQL SERVER定時刪除以前的資料 並備份（曆史庫）

 

create proc P_DataClear
(
 @clearTime datetime --清理的時間
)
as
--查詢出需要刪除的資料訂單號
declare @outOrderNB table(CustomerOrderNo varchar(12));
insert into @outOrderNB
select dbo.tbl_POD.CustomerOrderNo from dbo.tbl_POD
where dbo.tbl_POD.WaybillState='9' and convert(nvarchar(7), actualcompleteTime,120) = convert(nvarchar(7),@clearTime,120)

 

--插入曆史資料到在途曆史表
insert into dbo.PODHistory select
[BillNo],
[ActualShipTime],
[DeliveryTime],
[ActualCompleteTime],
[RequireBillReturnTime],
[BillReturnTime],
[BeginAddressTime],
[ReturnType],
[ReturnInformTime],
[ReturnNeedBackTime],
[IntendBoxNumber],
[BoxNumber],
[BookingNumber],
[TotalQty],
[TotalWeight],
tbl_POD.[CustomerOrderNo],
[BalanceAmount],
[TotalGrossWeight],
[Length],
[Width],
[Height],
[TotalCubage],
[BalancerCode],
[BalancerName],
[BalancerContact],
[BalancerTEL],
[EndCityName],
[BeginCityName],
[BeginAddress],
[ReceiverCode],
[ReceiverName],
[ReceiverContact],
[ReceiverTEL],
[EndAddressTEL],
[CustomerFax],
[Province],
[OrderTypes],
[City],
[EndAddress],
[AcceptOrderPrice],
[RecieveFeeRemark],
[Remarks],
[IsUrgency],
[BizMode],
[CustomerCode],
[CustomerName],
[ShipperCode],
[Status],
[SMS_Status],
[SMS_Frequency],
[SMS_LastSendTime],
[TEL_Status],
[TEL_Frequency],
[TEL_LastSendTime],
[Receipt_SignOne],
[Receipt_Remarks],
tbl_POD.[colCreater],
tbl_POD.[colCreateTime],
tbl_POD.[colUpdater],
tbl_POD.[colUpdateTime],
[AuthorizationNumber],
[TicketNumber],
[Notice3PLTime],
[ActualDeliveryTime],
[CustType],
[CustCode],
[DocTypes],
[Payment],
[ArrivalCityLevel],
[IsKA],
[Vehicle/LTL],
[TransitTimeLimit],
[Abbreviation],
tbl_POD.[AuditState],
[WaybillState],
[GoodsCode],
[Goods],
[Unit],
[UnitName],
[ExpectedShipAmount],
[ActualShipAmount],
tbl_PODTrack.[ArrivalTime],
[HandoverTime],
[OrderType],
[Remark],
[CreateTime],
[Creater]
FROM         dbo.tbl_POD INNER JOIN
                      dbo.tbl_POD_Details ON dbo.tbl_POD.CustomerOrderNo = dbo.tbl_POD_Details.CustomerOrderNo INNER JOIN
                      dbo.tbl_Receipt ON dbo.tbl_POD.CustomerOrderNo = dbo.tbl_Receipt.CustomerOrderNo inner JOIN
      dbo.tbl_PODTrack ON dbo.tbl_POD.CustomerOrderNo=dbo.tbl_PODTrack.colCustOrderNo
     AND dbo.tbl_POD_Details.CustomerOrderNo = dbo.tbl_Receipt.CustomerOrderNo
where dbo.tbl_Receipt.AuditState=1 and dbo.tbl_PODTrack.AuditState=1 and tbl_PODTrack.[ArrivalTime] is not null
and dbo.tbl_POD.CustomerOrderNo in (select CustomerOrderNo from @outOrderNB)
order by CustomerOrderNo

 

--插入曆史資料到在途曆史表
insert into dbo.PODTrackHistory  select
[colOrderNo],
[colCustOrderNo],
[colTrackTime],
[colIsNormal],
[colTransStatus],
[colTrackInfo],
[colTrackComment],
[colResponsibilityOwner],
[colGoodsStatus],
[colActualNoReceive],
[colProvince],
[colCity],
[colCreater],
[colCreateTime],
[colUpdater],
[colUpdateTime],
[Abnormal],
[UploadTime],
[AuditState]
from dbo.tbl_PODTrack
where [colCustOrderNo] in (select CustomerOrderNo from @outOrderNB)

 

--刪除曆史資料
delete  from dbo.tbl_POD where CustomerOrderNo in (select CustomerOrderNo from @outOrderNB)
delete  from dbo.tbl_POD_Details where CustomerOrderNo in (select CustomerOrderNo from @outOrderNB)
delete  from dbo.tbl_Receipt where CustomerOrderNo in (select CustomerOrderNo from @outOrderNB)
delete  from dbo.tbl_PODTrack where colCustOrderNo in (select CustomerOrderNo from @outOrderNB)