SQL server writes archive general template script to automatically delete data in batches

Time:2020-10-1

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

According to this situation, this week’s blogger (zhang502219048) just in the process of work, summed up and wrote a template to automatically delete data in batches. The template is fixed and unchanged. You only need to focus on the delete statement, and you can control the amount of data deleted in each batch in the delete statement. It is more convenient to assemble template SQL through variables, so as to avoid writing each table separately A duplicate code of batch logic can be simplified to complex. Adding a batch to delete the specified data of a table only needs to add a few lines of code (Demo1 and demo2 shown below).

Demo1: without parameters, according to table tmp_ Del deletes the data of ID corresponding to table a.

Demo2: with a parameter, the corresponding data of table B is deleted according to whether the date field is expired.

For details, please refer to the following script and related instructions. If you don’t understand, you are welcome to comment or private message to consult bloggers.

--= = = 1 batch 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 parameters
			3. Pass in the parameter: @ strstepinfo needs the step information of print
			4. Archive logic focuses on @ SQL_ Del, not dispersed in 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 -- the number of rows to be deleted. The initial value is - 1, followed by the number of rows deleted in each 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) = '
			' [email protected]_ Del scripts need to be written 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
			Wait for delay ''0:00:01' '-- delay
			print convert(varchar(50), getdate(), 121)
			print ''end batch''
			select @iBatch = @iBatch + 1
			end'
			--= = = = 2 Demo1 (delete statement does not contain parameters): Archive table 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 table 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 table a'
			--= = = = 3 demo2 (delete statement with parameters): Archive table b=======================================================
			select @parameters = @parameters_Base + ', @ArchiveDaate datetime' 
			--If you need to add custom parameters, add them here, for example @ parameters = @ parameters_ Base + ', @ArchiveDate datetime'
			, @sql_Del = '
			delete top (50000) 
			From table b
			where Date < @ArchiveDate
			'
			select @sql = @sql_Part1 + @sql_Del + @sql_Part2
			print @sql
	exec sp_ Executesql @ SQL, @ parameters, n '3 archive table B', @ archivedate

summary

The above is the introduction of SQL server to you. Write the archive general template script to automatically delete the data in batches. I hope it will help you. If you have any questions, please leave me a message, and the editor will reply to you in time!

Recommended Today

On the theoretical basis of SRE

What is SRE? When I first got into contact with SRE, many people thought that it was a post with full stack capability in Google and could solve many problems independently. After in-depth exploration, it is found that SRE can solve many problems, but there are too many problems. It is difficult for a post […]