Shulou(Shulou.com)05/31 Report--

This article introduces how to automatically delete data in batches in sql server. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Bloggers have done archive scripting for many projects. For this kind of script development to delete data, you must start with the simplest delete statement, and then because of the large amount of data in some tables and more indexes, you will find that deleting data is very slow and affects the normal use of the system. Then the delete statement is rewritten in batches of delete by a uniform amount of data, so that the original delete a table with a statement, it may become dozens of rows, if the archive table has more than a dozen or even dozens, then our script space is very large, increasing the cost of development and maintenance, is not conducive to less experienced new colleagues to develop archive scripts, but also easy to distract attention to the so-called batch logic.

According to this situation, this week the blogger (zhang502219048) happened to be working in the process of summarizing and writing a template for automatically deleting data in batches. The template is fixed and only needs to focus on the delete statement, and the amount of data deleted in each batch can be controlled in the delete statement. It is more convenient to assemble the template sql through variables to avoid writing a repetitive code of batch logic for each table. To add and delete a table in batches, you only need to add a few lines of code (demo1 and demo2 in the following).

Demo1: delete the data of table A corresponding to ID according to table tmp_Del without parameters.

Demo2: delete the corresponding data in Table B according to whether the Date field expires with parameters.

For details, please refer to the script and related instructions below. If you don't understand, you are welcome to comment or consult the blogger by private message.

-- = 1 batch archive template = =-- [Please do not modify the content of this template] / * description: 1. The assembled archive statement is @ sql = @ sql_Part1 + @ sql_Del + @ sql_Part22. The assembled parameter @ parameters is: @ parameters = @ parameters_Base + custom parameter 3. Input parameter: @ strStepInfo requires the step information of print. 4. Archive logic focuses on @ sql_Del rather than scattered in batches. * / declare @ parameters nvarchar (max) ='', @ parameters_Base nvarchar (max) = N'@strStepInfo nvarchar', @ sql nvarchar (max) ='', @ sql_Part1 nvarchar (max) = N'declare @ iBatch int = 1,-- batch @ iRowCount int =-1-- number of rows deleted, initially-1 Then take @ @ ROWCOUNTprint convert (varchar (50), getdate (), 121) + @ strStepInfowhile @ iRowCount 0begin print 'begin batch:'' print @ iBatch print convert (varchar (50), getdate (), 121) begin try begin tran', @ sql_Del nvarchar (max) =''- @ sql_Del script needs 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 waitfor delay '0select 01mm'-- delay print convert (varchar (50), getdate () Print''end batch'' select @ iBatch = @ iBatch + 1 endgame color-= 2 demo1 (delete statement does not contain parameters): archive table A = select @ parameters = @ parameters_Base +''- add custom parameters if necessary Add it 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_Part2print @ sqlexec sp_executesql @ sql, @ parameters, N' 2 archive Table Achille-= 3 demo2 (delete statement contains parameters): archive Table B = select @ parameters = @ parameters_Base +' @ ArchiveDaate datetime'-- add custom parameters if necessary Add here, for example, @ parameters = @ parameters_Base +', @ ArchiveDate datetime', @ sql_Del = 'delete top (50000) from table B where Date < @ ArchiveDate'select @ sql = @ sql_Part1 + @ sql_Part2print @ sqlexec sp_executesql @ sql, @ parameters, N' 3 archive table, @ ArchiveDate about how to automatically delete data in batches in sql server. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.