Dynamics Community Forum Thread Details

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