Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to automatically delete data in batches when writing archive general template script by sql server

2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

This article mainly introduces the sql server preparation of archive general template script how to automatically delete data in batches, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian with you to understand.

Archive scripting has been done for many projects, for this kind of script development of deleting data, it must be the simplest delete statement at the beginning, and then because of the large amount of data in some tables and more 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 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, a template is written to automatically delete 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 following script and related instructions

-- = 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 B where Date < @ ArchiveDate'select @ sql = @ sql_Part1 + @ sql_Part2print @ sqlexec sp_executesql @ sql, @ parameters, N' 3 archive, @ ArchiveDate thank you for reading this article carefully I hope the article "how to automatically delete data in batches when sql server writes an archive general template script" shared by the editor is helpful to everyone. At the same time, I also hope that you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.

Views: 235

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report