How do I delete a large number of records in smaller batches?


--Batch Size controls the number of records deleted per loop

SET @BatchSize = 20;

--Total Records to Delete allows the process to complete before all records that meet the criteria are deleted
--Set to a large number to delete all records meeting the critera
DECLARE @TotalRecordsToDelete INT;

SET @TotalRecordsToDelete = 1000000;

DECLARE @RecordsDeleted INT;
DECLARE @TotalRecordsDeleted INT;
DECLARE @Complete BIT;

SET @TotalRecordsDeleted=0;
SET @Complete=0;

while @Complete = 0

    delete top(@BatchSize)
    --Repalce {TableName} with the table to process
    from {TableName}
    --Replace {Filter} with the filter to control which records to delete
    where {Filter}

    Set @RecordsDeleted=@@ROWCOUNT
    SEt @TotalRecordsDeleted=@TotalRecordsDeleted + @RecordsDeleted

    select @RecordsDeleted DeletedRows,@TotalRecordsDeleted TotalDeletedRows

    if @RecordsDeleted=0 or @TotalRecordsDeleted>=@TotalRecordsToDelete
        set @Complete=1