0

There are several examples all over the Internet and StackOverflow on how to properly delete 500k+ rows from a single table and COMMIT those DELETES in Batches.

I am unsure of how to incorporate these examples into a much larger more complex script that DELETEs related data from multiple tables or how to do it properly while also providing meaningful incremental Output.

Back Story: My original script which only includes basic deletes based off of data loaded into a Temp Table took 54 minutes and locked up my entire Train database and in turn locked up the application. I absolutely cannot run this script in Production however I need to and I need to do it without locking resources.

Original DELETE Script. I removed many tables in this Example but listed how many tables are in the original script for reference

/* This version locks recourses and takes 54 minutes to run in Train.  This will lock up my Production Database and application. */
BEGIN

    BEGIN TRY
        BEGIN TRANSACTION

            DROP TABLE IF EXISTS #ImportedAssessments

            SELECT a.AssessmentID,a.ClientID
            INTO #ImportedAssessments
            FROM dbo.Assessment a
            INNER JOIN dbo.Clients c ON c.ClientID = a.ClientID 
            INNER JOIN [_ConversionData].[Schema].[ParticipantData] p ON p.pcc_id = c.ImportReferenceID

        -------     Client Interview 4 Tables   ------------------


            DELETE cs
            FROM CT_CurrLivingStatus cs
            INNER JOIN CT_ClientInterview ci ON ci.ClientInterviewID = cs.ClientInterviewID
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID

            DELETE er
            FROM CT_SvcEntryRisk er
            INNER JOIN CT_ClientInterview ci ON ci.ClientInterviewID = er.ClientInterviewID 
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID

            DELETE s
            FROM CT_ClientInterview ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
            INNER JOIN dbo.Service s ON s.TableID = ci.ClientInterviewID AND s.TableName =  'CT_ClientInterview'

            DELETE ci
            FROM CT_ClientInterview ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID

        -------     Brief Risk  Assessment 5 Tables ------------------

            DELETE  ro
            FROM CT_RiskAssessment ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
            INNER JOIN CT_RiskOptions ro ON ro.ParentID = ci.RiskAssessmentID

            --Tables removed for example

        ----------------------  HIVHCV Testing 12 Tables        ----------------------------    


            DELETE pr
            FROM CT_HIVHCVTesting ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
            INNER JOIN CT_HIVPositiveReferrals pr ON pr.ParentID = ci.HIVHCVTestingID

            --Tables removed for example

        ----------------------  Referrals Education 3 Tables    ----------------------------    


            DELETE ep
            FROM CT_ReferralsEducation ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
            INNER JOIN CT_EducationProvided ep ON ep.ParentID = ci.ReferralsEducationID


            --Tables removed for example


        ----------------------  SSP SyringeServices 3 Tables    ----------------------------    

            DELETE sd
            FROM CT_SyringeServices ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
            INNER JOIN CT_SyringesDisposedHow sd ON sd.ParentID = ci.SyringeServicesID

            --Tables removed for example

        ----------------------  SSP FentanylTestStrip 5 Tables  ----------------------------    

            DELETE du
            FROM CT_FentanylAssessment ci
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
            INNER JOIN CT_FentanylTestStrips ts ON ts.FentanylAssessmentID = ci.FentanylAssessmentID    
            INNER JOIN CT_DrugUses du ON du.ParentID = ts.FentanylTestStripsID

            --Tables removed for example


        -----------     Master Assessment   2 Tables    -------------------------------

            DELETE ce
            FROM dbo.CUSTOM_Assessment ce
            INNER JOIN #ImportedAssessments a ON a.AssessmentID = ce.AssessmentID

            --Table removed for example

        ----------------------------------------------------------------------------------


    
        ROLLBACK; PRINT char(10) + 'NOTE: Transaction Rolled Back on Purpose (Testing)';
        --COMMIT; PRINT char(10) + 'NOTE: Transaction COMMITTED';
    
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK;
            PRINT 'Error occured: Open Transaction Detected.  Transaction ROLLED BACK';
        END
    
        /* DECLARE Individual Error Detail Variables */
        DECLARE @ErrorProcedure VARCHAR(200), @ErrorMessage     NVARCHAR(4000), @ErrorSeverity  VARCHAR(20), @ErrorLine     VARCHAR(20)
            , @ErrorState       VARCHAR(20) /* Error State = Level aka Severity Level */
        ;

        /* Store Individual Error Details in variables for later output */
        SELECT  @ErrorProcedure = ERROR_PROCEDURE(), @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
        --ERROR_NUMBER() AS ErrorNumber

        /* RAISE the Error */
        RAISERROR(N'An error occurred in %s Deleting Assessment Records.  Level %s, State %s, Line %s, ErrorMsg: %s', 11, 2, @ErrorProcedure, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorMessage);
        RETURN;
    
    END CATCH

END

My new DELETE Script includes many of the suggestions provided in chat. I've added to comments within the script what updates I applied. This new version no longer locks the database and users can still use the application while this script is running

/*
 | Added TRANSACTION LOGIC.  
 | Wraps each table in a WHILE LOOP and a TRANSACTION
 | DELETEs Records using TOP(4500) for batches of 4500 per COMMIT
 | Outputs Time Stats in real time using RAISERROR.
 | Pull the Primary Keys of the records to be deleted into a Table Variable named @Pkeys to later DELETE using EXISTS
 | Prefixed each table with the dbo schema
*/

SET XACT_ABORT ON;  /* SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. */


DECLARE @DateTimeBeg    DATETIME;    -- start datetime
DECLARE @DateTimeEnd    DATETIME;    -- end datetime

DECLARE @TimeString     VARCHAR(10); -- Store Time as a String for Output in RAISERROR

DECLARE @Rows           VARCHAR(10); -- For RAISERROR Output
DECLARE @RowsDeleted    INT;         -- For WHILE Loops

DECLARE @PKeys TABLE (TablePKey INT NOT NULL, UNIQUE (TablePKey) ); -- Store Primary Keys of records slated for Deletion
DECLARE @PKeysLoaded    INT;         -- This is to avoid loading the @PKeys Table Variable more than once while in the WHILE Loop

DECLARE @CRLF           VARCHAR(4) = CHAR(13) + CHAR(10);

BEGIN

    BEGIN TRY

    /* 34 Tables Total */
    
        SELECT @DateTimeBeg = GETDATE();
    
            /* Output Time Stats - Process BEGIN */
            SELECT @TimeString = CONVERT(VARCHAR(25), @DateTimeBeg , 108);
            RAISERROR(N'Process Started at %s', 0, 1, @TimeString ) WITH NOWAIT; WAITFOR DELAY '00:00:01';

            DROP TABLE IF EXISTS #ImportedAssessments

            SELECT a.AssessmentID, a.ClientID
            INTO #ImportedAssessments
            FROM dbo.Assessment a
            INNER JOIN dbo.Client c ON c.ClientID = a.ClientID 
            INNER JOIN [_ConversionData].[Schema].[ParticipantData] p ON p.pcc_id = c.ImportReferenceID
            ;

            -- Create a non-clustered index on the 'AssessmentID' column of the temp table
            CREATE NONCLUSTERED INDEX IX_ImportedAssessments_AssessmentID ON #ImportedAssessments (AssessmentID);


        -------     Client Interview 4 Tables       ------------------


        RAISERROR(N'%sClient Interview Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';
    
        /* Table 1/4 CT_CurrLivingStatus */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION
            
                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT cs.CurrLivingStatusID
                FROM dbo.CT_CurrLivingStatus cs
                INNER JOIN dbo.CT_ClientInterview ci ON ci.ClientInterviewID = cs.ClientInterviewID
                INNER JOIN #ImportedAssessments    a ON a.AssessmentID = ci.AssessmentID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) cs
                FROM dbo.CT_CurrLivingStatus cs
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = cs.CurrLivingStatusID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_CurrLivingStatus %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/4 CT_SvcEntryRisk */
        --SQL Removed for Example

        /* Table 3/4 Service Records for CT_ClientInterview */
        --SQL Removed for Example

        /* Table 4/4 CT_ClientInterview */
        --SQL Removed for Example

        -------     Brief Risk  Assessment 5 Tables ------------------


        RAISERROR(N'%sBrief Risk Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';

        /* Table 1/5 CT_RiskOptions */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT ro.RiskOptionsID
                FROM dbo.CT_RiskAssessment ci
                INNER JOIN #ImportedAssessments a ON a.AssessmentID = ci.AssessmentID
                INNER JOIN dbo.CT_RiskOptions  ro ON ro.ParentID = ci.RiskAssessmentID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) ro
                FROM dbo.CT_RiskOptions ro
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = ro.RiskOptionsID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_RiskAssessment %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/5 CT_UsedSubstances */
        --SQL Removed for Example
    
        /* Table 3/5 CT_TobaccoTypes */
        --SQL Removed for Example

        /* Table 4/5 CT_CannabisTypes */
        --SQL Removed for Example

        /* Table 5/5 CT_RiskAssessment */
        --SQL Removed for Example


        ----------------------  HIVHCV Testing 12 Tables        ----------------------------


        RAISERROR(N'%sHIVHCV Testing Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';


        /* Table 1/12 CT_HIVPositiveReferrals */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT pr.HIVPositiveReferralsID
                FROM dbo.CT_HIVHCVTesting hi
                INNER JOIN #ImportedAssessments         a ON a.AssessmentID = hi.AssessmentID
                INNER JOIN dbo.CT_HIVPositiveReferrals pr ON pr.ParentID    = hi.HIVHCVTestingID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) pr
                FROM dbo.CT_HIVPositiveReferrals pr
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = pr.HIVPositiveReferralsID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_HIVPositiveReferrals %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/12 CT_Symptoms */
        --SQL Removed for Example

        /* Table 3/12 CT_TestingStatus */
        --SQL Removed for Example

        /* Table 4/12 CT_HCVContacts */
        --SQL Removed for Example

        /* Table 5/12 CT_PreTestActivities */
        --SQL Removed for Example

        /* Table 6/12 CT_TattooLocations */
        --SQL Removed for Example

        /* Table 7/12 CT_PiercingLocations */
        --SQL Removed for Example

        /* Table 8/12 CT_PreTestStatuses */
        --SQL Removed for Example

        /* Table 9/12 CT_Facilities */
        --SQL Removed for Example

        /* Table 10/12 CT_HCVPositiveReferrals */
        --SQL Removed for Example

        /* Table 11/12 Service records for CT_HIVHCVTesting */
        --SQL Removed for Example

        /* Table 12/12 CT_HIVHCVTesting */
        --SQL Removed for Example


        ----------------------  Referrals Education 3 Tables    ----------------------------


        RAISERROR(N'%sReferrals Education Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';


        /* Table 1/3 CT_EducationProvided */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT ep.EducationProvidedID
                FROM dbo.CT_ReferralsEducation ci
                INNER JOIN #ImportedAssessments      a ON a.AssessmentID = ci.AssessmentID
                INNER JOIN dbo.CT_EducationProvided ep ON ep.ParentID    = ci.ReferralsEducationID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) ep
                FROM dbo.CT_EducationProvided ep
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = ep.EducationProvidedID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_EducationProvided %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/3 CT_ReferralsProvided */
        --SQL Removed for Example

        /* Table 3/3 CT_ReferralsEducation */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT re.ReferralsEducationID
                FROM dbo.CT_ReferralsEducation re
                INNER JOIN #ImportedAssessments a ON a.AssessmentID = re.AssessmentID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) re
                FROM dbo.CT_ReferralsEducation re
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = re.ReferralsEducationID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_ReferralsEducation %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END


        ----------------------  SSP SyringeServices 3 Tables    ----------------------------


        RAISERROR(N'%sSyringe Services Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';


        /* Table 1/3 CT_SyringesDisposedHow */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT sd.SyringesDisposedHowID
                FROM dbo.CT_SyringeServices ci
                INNER JOIN #ImportedAssessments        a ON a.AssessmentID = ci.AssessmentID
                INNER JOIN dbo.CT_SyringesDisposedHow sd ON sd.ParentID = ci.SyringeServicesID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) sd
                FROM dbo.CT_SyringesDisposedHow sd
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = sd.SyringesDisposedHowID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_SyringesDisposedHow %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/3 Service records for CT_SyringeServices */
        --SQL Removed for Example

        /* Table 3/3 CT_SyringeServices */
        --SQL Removed for Example


        ----------------------  SSP FentanylTestStrip 5 Tables  ----------------------------


        RAISERROR(N'%sFentanyl Test Strips Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';


        /* Table 1/5 CT_DrugUses */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT du.DrugUsesID
                FROM dbo.CT_FentanylAssessment ci
                INNER JOIN #ImportedAssessments      a ON a.AssessmentID           = ci.AssessmentID
                INNER JOIN dbo.CT_FentanylTestStrips ts ON ts.FentanylAssessmentID = ci.FentanylAssessmentID    
                INNER JOIN dbo.CT_DrugUses           du ON du.ParentID             = ts.FentanylTestStripsID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) du
                FROM dbo.CT_DrugUses du
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = du.DrugUsesID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CT_DrugUses %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/5 CT_OverdoseOutcomes */
        --SQL Removed for Example

        /* Table 3/5 CT_TestedDrugs */
        --SQL Removed for Example

        /* Table 4/5 CT_FentanylTestStrips */
        --SQL Removed for Example

        /* Table 5/5 CT_FentanylAssessment */
        --SQL Removed for Example


        ----------------------  Master Assessmnet 2 Tables      ----------------------------


        RAISERROR(N'%sMaster Assessmnet Section DELETE', 0, 1, @CRLF) WITH NOWAIT; WAITFOR DELAY '00:00:01';


        /* Table 1/2 CUSTOM_Assessment */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT ca.AssessmentID
                FROM dbo.CUSTOM_Assessment ca
                INNER JOIN #ImportedAssessments a ON a.AssessmentID = ca.AssessmentID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) ca
                FROM dbo.CUSTOM_Assessment ca
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = ca.AssessmentID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'CUSTOM_Assessment %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        /* Table 2/2 Assessment */
        DELETE FROM @PKeys;     -- Reset Table Variable
        SET @PKeysLoaded   = 0; -- Reset PKeysLoaded for the next WHILE Loop
        SET   @RowsDeleted = 1; -- Set to 1 to start WHILE Loop
        WHILE @RowsDeleted > 0
        BEGIN
            BEGIN TRANSACTION

                /* Gather PKeys into PKeys table variable for DELETion */
                IF @PKeysLoaded = 0   /* If @PKeysLoaded = 0 this means the data has not yet been loaded into @PKeys */
                BEGIN
                INSERT INTO @PKeys (TablePKey)
                SELECT aa.AssessmentID
                FROM dbo.Assessment aa
                INNER JOIN #ImportedAssessments a ON a.AssessmentID = aa.AssessmentID
                ;
                SET @PKeysLoaded = 1; /* Used to avoid loading the @PKeys TABLE Variable more than once WHILE in the Loop */
                END

                DELETE TOP(4500) aa
                FROM dbo.Assessment aa
                WHERE EXISTS (SELECT 1 FROM @PKeys pk WHERE pk.TablePKey = aa.AssessmentID )

                SET    @RowsDeleted = @@ROWCOUNT;
                SELECT @TimeString  = ISNULL( CONVERT(VARCHAR(25), GETDATE() , 108), 'NoTime' )
                        , @Rows     = ISNULL( CAST(@RowsDeleted AS VARCHAR(10)), 'NoRows' )
                ;
                RAISERROR(N'Assessment %s. Rows: %s', 0, 1, @TimeString, @Rows) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

            COMMIT TRANSACTION;
        END

        ------------------------------------------------------


        /* Output Time Stats */
        SET @DateTimeEnd = GETDATE();
    
        SELECT @TimeString = CONVERT(VARCHAR(25), @DateTimeEnd , 108);
        RAISERROR(N'%sProcess Completed at %s', 0, 1, @CRLF, @TimeString) WITH NOWAIT;  WAITFOR DELAY '00:00:01';

        -- Calculate the difference in seconds
        DECLARE @DiffInSeconds INT = DATEDIFF(SECOND, @DateTimeBeg, @DateTimeEnd);

        -- Calculate hours, minutes, and seconds from the total seconds
        DECLARE @Hours INT = @DiffInSeconds / 3600;
        DECLARE @Minutes INT = (@DiffInSeconds % 3600) / 60;
        DECLARE @Seconds INT = @DiffInSeconds % 60;

        -- Combine hours, minutes, and seconds into a time string
        DECLARE @TotalTimeString VARCHAR(8) = 
            RIGHT('0' + CAST(@Hours AS VARCHAR(2)), 2) + ':' +
            RIGHT('0' + CAST(@Minutes AS VARCHAR(2)), 2) + ':' +
            RIGHT('0' + CAST(@Seconds AS VARCHAR(2)), 2);

        RAISERROR(N'%sTotal Time: %s', 0, 1, @CRLF, @TotalTimeString) WITH NOWAIT; WAITFOR DELAY '00:00:01';

    
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK;
            PRINT 'Error occured: Open Transaction Detected.  Transaction ROLLED BACK';
        END
    
        /* DECLARE Individual Error Detail Variables */
        DECLARE @ErrorProcedure VARCHAR(200), @ErrorMessage     NVARCHAR(4000), @ErrorSeverity  VARCHAR(20), @ErrorLine     VARCHAR(20)
            , @ErrorState       VARCHAR(20) /* Error State = Level aka Severity Level */
        ;

        /* Store Individual Error Details in variables for later output */
        SELECT  @ErrorProcedure = ERROR_PROCEDURE(), @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
        --ERROR_NUMBER() AS ErrorNumber

        /* RAISE the Error */
        RAISERROR(N'An error occurred in %s Deleting Assessment Records.  Level %s, State %s, Line %s, ErrorMsg: %s', 11, 2, @ErrorProcedure, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorMessage);
        RETURN;
    
    END CATCH

END

I've included links below as to what led me to create this NEW version. Again I did not discover any examples that show how to do this in the real world with a Complex example DELETING from multiple tables in a single script

https://stackoverflow.com/questions/24785439/deleting-1-millions-rows-in-sql-server

https://stackoverflow.com/questions/11230225/how-to-efficiently-delete-rows-while-not-using-truncate-table-in-a-500-000-rows

https://stackoverflow.com/questions/2138593/delete-large-amount-of-data-in-sql-server

https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.