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, however changes were made to the database format in OOB v2.78D so this query only works for OOB v2.78D+. Transactions and activities linked to other companies will remain untouched.
For a query that works with OOB v2.72 to OOB v2.78C purge_all_transactions_for_test_company_oobv2.72 .
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 TransPart DROP CONSTRAINT [FK_TransPart_TransDetail] ; -- Disable Constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" ; 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 @MaxID INT; SET @MaxID = (SELECT ISNULL(MAX(ID),0)+1 FROM SetupStepsLog) INSERT INTO SetupStepsLog (ID, ModifiedDate, ModifiedByComputer, ModifiedByUser, Description) VALUES( @MaxID , GetDate() , HOST_Name() , SUSER_NAME() , 'Purge All Info Related to Test Company' ) ; -- Enable Constraints EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" ; SELECT 'Yes'
Version Information
- Revised : 10/06/2014
- Version : 2.2
You could leave a comment if you were logged in.