SQL Server regularly deletes and backs up previous data (historical database)

 

Create proc p_dataclear
(
@ Cleartime datetime -- cleaning time
)
As
-- Query the Data Order number to be deleted
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 historical data to the path History Table
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_receept 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 historical data to the path History Table
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 historical data
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_receept where customerorderno in (select customerorderno from @ outordernb)
Delete from DBO. tbl_podtrack where colcustorderno in (select customerorderno from @ outordernb)