Announcements
Our RMS database is getting rather large as we have been on RMS for years. We are currently on version 2.0.
There seems to be no procedure within RMS to purge sales transactions, though I may have missed it.
I did find a procedure to purge RMS sales transactions via a SQL script, but it stated it was good up to version 1.3.
I am not crazy enough to assume that a SQL script for up to version 1.3 will work for version 2.0 and later.
Does anyone know of a method to purge sales transactions in RMS version 2? I am hoping I have missed a KB article or some other note already published.
Any assistance would be appreciated.
*This post is locked for comments
Here is a long list of queries from an old post - to clean up an old database:
Backup database first and use carefully.
Marc
"Randy" wrote:
>
> What is the best practice for purging old transactions? I have inherited a
> database that has data that is ten years old
For a complete purge of old records:
Order Table Command
1 RecordDeletedLog DELETE from RecordDeletedLog where WhenDeleted <
'01/01/2000'
2 InventoryTransferLog DELETE from InventoryTransferLog where
DateTransferred < '01/01/2000'
3 PurchaseOrderEntryDetail DELETE from PurchaseOrderEntryDetail where Date <
'01/01/2000'
4 PurchaseOrderEntry DELETE POE FROM PurchaseOrderEntry POE LEFT JOIN
PurchaseOrder PO ON POE.PurchaseOrderID=PO.ID AND POE.StoreID=PO.StoreID
WHERE PO.DateCreated<'01/01/2000'
5 PurchaseOrder DELETE from PurchaseOrder where datecreated < '01/01/2000'
6 QuoteTenderEntry DELETE QTE from QuoteTenderEntry QTE join [Order] O on
QTE.OrderID=O.ID WHERE O.Time<'01/01/2000'
7 OrderHistory DELETE OH FROM OrderHistory OH LEFT JOIN [Order] O ON
(OH.OrderID = O.ID) AND (OH.StoreID = O.StoreID) WHERE (O.Time)<'01/01/2000'
8 OrderEntry DELETE OE FROM OrderEntry OE LEFT JOIN [Order] O ON (OE.OrderID
= O.ID) AND (OE.StoreID = O.StoreID) WHERE (O.Time)<'01/01/2000'
9 Order DELETE from [order] where Time < '01/01/2000'
(10-11 don't delete accountreceivable as will affect current account balances)
12 Shipping DELETE S FROM Shipping S LEFT JOIN [Transaction] T ON
S.TransactionNumber=T.TransactionNumber AND S.StoreID=T.StoreID WHERE
T.Time<'01/01/2000'
13 VoucherEntry DELETE from VoucherEntry where Date < '01/01/2000'
14 TaxEntry DELETE TE FROM TaxEntry TE LEFT JOIN [Transaction] T ON
TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE
T.Time<'01/01/2000'
15 TransactionEntry DELETE TE FROM TransactionEntry TE LEFT JOIN
[Transaction] T ON TE.TransactionNumber=T.TransactionNumber AND
TE.StoreID=T.StoreID WHERE T.Time<'01/01/2000'
17 NonTenderTransaction DELETE from NonTenderTransaction where Time <
'01/01/2000'
18 Payment DELETE from Payment where Time < '01/01/2000'
19 DropPayout DELETE from DropPayout where Time < '01/01/2000'
20 TenderEntry DELETE TE FROM TenderEntry TE LEFT JOIN [Transaction] T ON
TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE
T.Time<'01/01/2000'
21 Transaction DELETE from [Transaction] where Time < '01/01/2000'
22 OldJournal DELETE from OldJournal where ClosingTime < '01/01/2000'
23 Journal DELETE from Journal where Time < '01/01/2000'
24 Batch DELETE from Batch where ClosingTime < '01/01/2000'
25 DailySales DELETE from DailySales where Date < '01/01/2000'
26 TimeCard DELETE from TimeCard where TimeOut < '01/01/2000'
27 TimeStampLog DELETE from TimeStampLog where ServerTime < '01/01/2000'
28 HQRecordDeletedLog DELETE from HQRecordDeletedLog where WhenDeleted <
'01/01/2000'
29 Worksheet_DelGlobalCustomers DELETE W from Worksheet_DelGlobalCustomers W
JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
30 Worksheet_GlobalAccountAdjustment DELETE W from
Worksheet_GlobalAccountAdjustment W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
31 Worksheet_InventoryTransfer DELETE W from Worksheet_InventoryTransfer W
JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
32 Worksheet_ItemCost DELETE W from Worksheet_ItemCost W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
33 Worksheet_ItemPrice DELETE W from Worksheet_ItemPrice W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
34 Worksheet_ItemPriceLevel DELETE W from Worksheet_ItemPriceLevel W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
35 Worksheet_ItemPriceLimit DELETE W from Worksheet_ItemPriceLimit W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
36 Worksheet_ItemQuantity DELETE W from Worksheet_ItemQuantity W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
37 Worksheet_ItemRestock DELETE W from Worksheet_ItemRestock W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
38 Worksheet_ItemTax DELETE W from Worksheet_ItemTax W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
39 Worksheet_ItemUpdate DELETE W from Worksheet_ItemUpdate W JOIN Worksheet
on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
40 Worksheet_PurchaseOrder DELETE W from Worksheet_PurchaseOrder W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
41 Worksheet_SQL DELETE W from Worksheet_SQL W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
42 Worksheet_SupplierList DELETE W from Worksheet_SupplierList W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
43 Worksheet_SupplierUpdate DELETE W from Worksheet_SupplierUpdate W JOIN
Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
44 WorksheetHeader_PurchaseOrder DELETE W from WorksheetHeader_PurchaseOrder
W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
45 WorksheetHistory DELETE W from WorksheetHistory W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
46 WorksheetStore DELETE W from WorksheetStore W JOIN Worksheet on
W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'
47 Worksheet DELETE from Worksheet WHERE EfectiveDate<'01/01/2000'
Alvin,
The script will likely still work as there was little changes to the database between 1.3 and 2.0 especially where the transaction is concerned. You may also wish to look into Retail Realms Archive utility that provides tools to archive a lot of old RMS data.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,802 Super User 2024 Season 2
Martin Dráb 229,133 Most Valuable Professional
nmaenpaa 101,154