Explanation of SQL
This query will delete all Transactions and their activities that are linked to the 'Test Company'. The 'Test Company' was added to the OOB v2.72 dataset, so this query is only available for OOB v2.72 to OOB v2.78C. Transactions and activities linked to other companies will remain untouched.
For a query that works with OOB v2.78D+ purge_all_transactions_for_test_company_oobv2.78d .
Risk of Data Corruption if Run Improperly
HIGH. This is a series of delete queries which will permanently delete data from your database. Be sure to create a backup of your database before running these queries.
Query for SQL
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_TransPart_TransDetail') AND parent_object_id = OBJECT_ID(N'dbo.TransPart') ) ALTER TABLE [dbo.TransPart] DROP CONSTRAINT [dbo.FK_TransPart_TransDetail] GO DECLARE @Confirmation VARCHAR(10); SET @Confirmation = 'Yes' -- Comment this line if you are inserting directly into Control's report setup. -- SET @Confirmation = '' -- Remove comments from this line if you are inserting directly into Control's report setup. UPDATE Account SET CreditBalance = 0 WHERE ID = 1001 AND (@Confirmation = 'Yes') ; UPDATE JOURNAL SET AccountID = (SELECT AccountID FROM Transheader WHERE ID = Journal.TransactionID) WHERE TransactionID IS NOT NULL AND (@Confirmation = 'Yes') ; UPDATE LEDGER SET AccountID = (SELECT AccountID FROM Transheader WHERE ID = Ledger.TransactionID) WHERE TransactionID IS NOT NULL AND (@Confirmation = 'Yes') ; DELETE FROM Ledger WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes') ; DELETE FROM Journal WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes') ; DELETE FROM ContactActivity WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Journal) AND (@Confirmation = 'Yes') ; DELETE FROM Payment WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Journal) AND (@Confirmation = 'Yes') ; DELETE FROM TransPart WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes') ; DELETE FROM TransDetailParam WHERE ID > 1000 AND ParentID IN( SELECT ID FROM TransDetail WHERE ID > 1000 AND TransheaderID IN (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) ) AND (@Confirmation = 'Yes') ; DELETE FROM TransMod WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes') ; DELETE FROM TransDetail WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes') ; DELETE FROM TransVariation WHERE ParentID IN (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes') ; DELETE FROM Transheader WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes') ; DELETE FROM VendorTransDetail WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes') ; DELETE FROM TransDetailGraphic WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Transdetail) AND (@Confirmation = 'Yes') ; DELETE FROM TransHeaderUserField WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes') ; DELETE FROM TransTax WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes') ; DELETE FROM Shipments WHERE ID > 1000 AND TransHeaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes') ; DELETE FROM PartUsageCard WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes') ; DELETE FROM InventoryLog WHERE ID > 1000 AND TransDetailID NOT IN (SELECT ID FROM TransDetail) AND (@Confirmation = 'Yes') ; DECLARE @NextID INT; SET @NextID = (SELECT COALESCE(MAX(ID)+1,1) FROM SetupStepsLog); INSERT INTO SetupStepsLog VALUES( @NextID , GetDate() , HOST_Name() , SUSER_NAME() , 'Purge All Info Related to Test Company' ) ; -- Select 'Yes' -- Remove comments from this line if you are inserting directly into Control's report setup.
Version Information
- Revised : 02/01/2013
- Version : 2.0
You could leave a comment if you were logged in.