The blogger has written archive scripts for many projects. For the script development of deleting data, it must be the simplest delete statement at the beginning. Then, because of the large amount of data in some tables and the large number of indexes, it will be found that deleting data is very slow and affects the normal use of the system. Then the delete statement is rewritten in batches according to the uniform data volume. In this way, the original deletion table with one statement may become dozens of rows. If there are dozens or even dozens of archive tables, our script space will be very large, which increases the cost of development and maintenance, and is not conducive to the development of archive scripts by new colleagues with less experience. It is also easy to distract attention from so-called batch logic.

According to this situation, this week's blogger (zhang 502219048) just in the process of working, summarized and compiled a template for automatically deleting data in batches, the template is fixed, only need to focus on delete statement, and can control the amount of deleted data in each batch in delete statement. It is more convenient to assemble the template sql through variables, so as to avoid each form being deleted. Write a single batch logic duplicate code, simplified to complex, add batch deletion of a table specified data only need to add a few lines of code (demo1 and demo2 as shown below).

demo1: Delete the ID data of table A according to table tmp_Del without parameters.

demo2: Delete table B corresponding data according to whether the Date field expires with parameters.

Please refer to the following script and related instructions, if you don't understand, you are welcome to comment or consult bloggers by private letter.

-- ===== 1 In batches archive Template =======================================================
--[Please do not modify the content of this template.
/* 
Explain:
1. The assembled archive statement is: @sql = @sql_Part1 + @sql_Del + @sql_Part2
2. The assembly parameter @parameters is: @parameters = @parameters_Base+ custom parameter
3. Input parameter: @strStepInfo requires step information for print
4. archive Logic focuses on @sql_Del, not on batches.
*/
declare @parameters nvarchar(max) = ''
, @parameters_Base nvarchar(max) = N'@strStepInfo nvarchar(100)'
, @sql nvarchar(max) = ''
, @sql_Part1 nvarchar(max) = N'
declare @iBatch int = 1,     --batch
        @iRowCount int = -1  --Delete rows, starting with-1，Later take the number of deleted rows per batch@@ROWCOUNT
print convert(varchar(50), getdate(), 121) + @strStepInfo

while @iRowCount <> 0
begin
    print ''begin batch:''
    print @iBatch
    print convert(varchar(50), getdate(), 121)
    
    begin try
        begin tran
'
, @sql_Del nvarchar(max) = '
'  --@sql_Del The script needs to be written on its own in subsequent scripts according to the actual situation.
, @sql_Part2 nvarchar(max) = N'    
            select @iRowCount = @@rowcount
        commit tran  
    end try
    begin catch
        rollback tran
        print ''-- Error Message: '' + convert(varchar, error_line()) + '' | '' + error_message()
    end catch

    waitfor delay ''0:00:01'' --delayed

    print convert(varchar(50), getdate(), 121)
    print ''end batch''

    select @iBatch = @iBatch + 1
end'

-- ===== 2 demo1(delete Statements do not contain parameters: archive surface A =======================================================
select @parameters = @parameters_Base + ''  --If you need to add custom parameters, add them here, for example@parameters = @parameters_Base + ', @ArchiveDate datetime'
, @sql_Del = '
            delete top (50000) tc_Del 
            from surface A tc_Del
            inner join tmp_Del cd on cd.ID = tc_Del.ID
'
select @sql = @sql_Part1 + @sql_Del + @sql_Part2
print @sql
exec sp_executesql @sql, @parameters, N' 2 archive surface A'

-- ===== 3 demo2(delete Statements with parameters: archive surface B =======================================================
select @parameters = @parameters_Base + ', @ArchiveDate datetime'  --If you need to add custom parameters, add them here, for example@parameters = @parameters_Base + ', @ArchiveDate datetime'
, @sql_Del = '
            delete top (50000) 
            from surface B
            where Date < @ArchiveDate
'
select @sql = @sql_Part1 + @sql_Del + @sql_Part2
print @sql
exec sp_executesql @sql, @parameters, N' 3 archive surface B', @ArchiveDate