Explanation of SQL
This query will delete all Transactions and their activities that are linked to the 'Test Company' which an Account.ID = 1001. If your test company has a different ID you can change the @AccountID parameter accordingly.
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
BEGIN TRANSACTION; BEGIN TRY 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] ; DECLARE @AccountID INT = 1001; DECLARE @Confirmation VARCHAR(10) = 'Yes'; --DECLARE @Confirmation VarChar(10) = ; -- Disable Constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" ; UPDATE ACCOUNT SET CreditBalance = 0 WHERE ID = @AccountID 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 = @AccountID AND (@Confirmation = 'Yes') ; DELETE FROM Journal WHERE ID > 1000 AND AccountID = @AccountID 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 = @AccountID) 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 = @AccountID) ) AND (@Confirmation = 'Yes') ; DELETE FROM TransMod WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes') ; DELETE FROM TransDetail WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes') ; DELETE FROM TransVariation WHERE ParentID IN (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes') ; DELETE FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkGroupTransDetailLink WHERE TransDetailID NOT IN (SELECT ID FROM TransDetail) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkGroup WHERE TransHeaderID NOT IN (SELECT ID FROM TransHeader WHERE ID > 1000) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkGroupStatusHistory WHERE ArtworkGroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkLog WHERE ArtworkGroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkNotificationHistory WHERE GroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkItem WHERE GroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkComment WHERE ArtworkItemID NOT IN (SELECT ID FROM ArtworkItem) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkProofFile WHERE ArtworkItemID NOT IN (SELECT ID FROM ArtworkItem) AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkDigestEntry WHERE GroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes') ; DELETE FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID AND (@Confirmation = 'Yes') ; DELETE FROM ArtworkPlayer WHERE TransHeaderID NOT IN (SELECT ID FROM TransHeader WHERE ID > 1000 AND ('Yes' = 'Yes')) ; DELETE FROM ArtworkPlayerRoleLink WHERE PlayerID NOT IN (SELECT ID FROM ArtworkPlayer) ; 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') ; IF @Confirmation = 'Yes' BEGIN EXEC dbo.[PrCA.Job.ReLinkAllTransactions]; END ELSE SELECT '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' ) ; -- Enable Constraints EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" ; SELECT 'Yes' ; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
Version Information
- Revised : 11/28/2017
You could leave a comment if you were logged in.