Delete Transaction History after Testing

V5 Question
21

Hi, thank you

Now it has cleared all transactions but the query still always ends with this error conflict:

(0 righe interessate)
Messaggio 547, livello 16, stato 0, riga 5
The DELETE statement conflicted with the REFERENCE constraint “FK_dbo.AccountTransactions_dbo.AccountTransactionDocuments_AccountTransactionDocumentId”. The conflict occurred in database “SambaPOS5”, table “dbo.AccountTransactions”, column ‘AccountTransactionDocumentId’.
The statement has been terminated.

it would be nice to have an error-free query - :slight_smile:

Thanks for the support

22

I’m on the beach right now. When I get back I’ll edit the script so there will be no errors. Stand by, thanks.

23

OK thanks, see you later

24

What the following will do is disable checking of all foreign key constraints for the tables that will be cleared. Once the data is removed, it will enable all checks for foreign keys. The last part will reset all identity columns (counters) to zero.

Let me know.

ALTER TABLE TicketEntities NOCHECK CONSTRAINT ALL;
ALTER TABLE Tickets NOCHECK CONSTRAINT ALL;
ALTER TABLE AccountTransactionDocuments NOCHECK CONSTRAINT ALL;
ALTER TABLE AccountTransactions NOCHECK CONSTRAINT ALL;
ALTER TABLE AccountTransactionValues NOCHECK CONSTRAINT ALL;
ALTER TABLE Calculations NOCHECK CONSTRAINT ALL;
ALTER TABLE CostItems NOCHECK CONSTRAINT ALL;
ALTER TABLE InventoryTransactionDocuments NOCHECK CONSTRAINT ALL;
ALTER TABLE InventoryTransactions NOCHECK CONSTRAINT ALL;
ALTER TABLE Orders NOCHECK CONSTRAINT ALL;
ALTER TABLE PaidItems NOCHECK CONSTRAINT ALL;
ALTER TABLE PeriodicConsumptionItems NOCHECK CONSTRAINT ALL;
ALTER TABLE PeriodicConsumptions NOCHECK CONSTRAINT ALL;
ALTER TABLE ProductTimerValues NOCHECK CONSTRAINT ALL;
ALTER TABLE Payments NOCHECK CONSTRAINT ALL;
ALTER TABLE WarehouseConsumptions NOCHECK CONSTRAINT ALL;
ALTER TABLE WorkPeriods NOCHECK CONSTRAINT ALL;
DELETE FROM TicketEntities;
DELETE FROM Tickets;
DELETE FROM AccountTransactionDocuments;
DELETE FROM AccountTransactions;
DELETE FROM AccountTransactionValues;
DELETE FROM Calculations;
DELETE FROM CostItems;
DELETE FROM InventoryTransactionDocuments;
DELETE FROM InventoryTransactions;
DELETE FROM Orders;
DELETE FROM PaidItems;
DELETE FROM PeriodicConsumptionItems;
DELETE FROM PeriodicConsumptions;
DELETE FROM ProductTimerValues;
DELETE FROM Payments;
DELETE FROM WarehouseConsumptions;
DELETE FROM WorkPeriods;
UPDATE Numerators SET Number = 0;
UPDATE EntityStateValues SET EntityStates = '{"S":"Available","SN":"Status"}' Where EntityStates like '%Status%';
ALTER TABLE TicketEntities CHECK CONSTRAINT ALL;
ALTER TABLE Tickets CHECK CONSTRAINT ALL;
ALTER TABLE AccountTransactionDocuments CHECK CONSTRAINT ALL;
ALTER TABLE AccountTransactions CHECK CONSTRAINT ALL;
ALTER TABLE AccountTransactionValues CHECK CONSTRAINT ALL;
ALTER TABLE Calculations CHECK CONSTRAINT ALL;
ALTER TABLE CostItems CHECK CONSTRAINT ALL;
ALTER TABLE InventoryTransactionDocuments CHECK CONSTRAINT ALL;
ALTER TABLE InventoryTransactions CHECK CONSTRAINT ALL;
ALTER TABLE Orders CHECK CONSTRAINT ALL;
ALTER TABLE PaidItems CHECK CONSTRAINT ALL;
ALTER TABLE PeriodicConsumptionItems CHECK CONSTRAINT ALL;
ALTER TABLE PeriodicConsumptions CHECK CONSTRAINT ALL;
ALTER TABLE ProductTimerValues CHECK CONSTRAINT ALL;
ALTER TABLE Payments CHECK CONSTRAINT ALL;
ALTER TABLE WarehouseConsumptions CHECK CONSTRAINT ALL;
ALTER TABLE WorkPeriods CHECK CONSTRAINT ALL;
DBCC CHECKIDENT ('WorkPeriods',RESEED,0);
DBCC CHECKIDENT ('TicketEntities',RESEED,0);
DBCC CHECKIDENT ('Tickets',RESEED,0);
DBCC CHECKIDENT ('AccountTransactionDocuments',RESEED,0);
DBCC CHECKIDENT ('AccountTransactions',RESEED,0);
DBCC CHECKIDENT ('AccountTransactionValues',RESEED,0);
DBCC CHECKIDENT ('Calculations',RESEED,0);
DBCC CHECKIDENT ('CostItems',RESEED,0);
DBCC CHECKIDENT ('InventoryTransactionDocuments',RESEED,0);
DBCC CHECKIDENT ('InventoryTransactions',RESEED,0);
DBCC CHECKIDENT ('Orders',RESEED,0);
DBCC CHECKIDENT ('PaidItems',RESEED,0);
DBCC CHECKIDENT ('PeriodicConsumptionItems',RESEED,0);
DBCC CHECKIDENT ('PeriodicConsumptions',RESEED,0);
DBCC CHECKIDENT ('ProductTimerValues',RESEED,0);
DBCC CHECKIDENT ('Payments',RESEED,0);
DBCC CHECKIDENT ('WarehouseConsumptions',RESEED,0);
2 Likes
25

perfect!!!
Thanks a lot for the support

26

Is there way I can delete certain transaction but not all transactions.

27

Nope. Transactions are intertwined all throughout the database. It’s all or nothing.

28

I would say, maybe do a backup of the database before testing, then restore to that database after testing? Closest thing to “deleting” test transactions.