SQL Ninja moves Data in Auto Balancer Loop

Hi, today I want to share with you one of my last invention. That is extremely useful when you move data, for example, to historical or archive tables on your SQL Server. Imagine that the same moment you move your data, someone can, with a lower probability, use the table you move data from. When you decide to insert data into a new table and then delete them, of course, in the delete query, you lock the whole table. Now imagine that you have 100k rows to delete. Of course, nobody can use your operating table because it is locked, right? So here I want to share with you an example of how to do that correctly and eat your elephant piece by piece.

use tempdb
go
-- count of test data set
declare @cnt int = 100000
if object_id('TestMoveFromTable') is not null
drop table TestMoveFromTable
create table TestMoveFromTable (
  ID bigint not null identity(1,1) primary key clustered,
  Value nvarchar(200) not null
)
if object_id('TestMoveToTable') is not null
drop table TestMoveToTable
create table TestMoveToTable (
  ID bigint not null primary key clustered,
  Value nvarchar(200) not null
)
;with n(n) as (
  select 1 n
  union all
  select n+1 from n where n < @cnt
)
insert into TestMoveFromTable(Value)
select 'Value' + str(cast(n as varchar(100)), 100) from n
option (maxrecursion 0)
-- move data with auto balancer loop begin
create table #set(ID bigint primary key clustered)
declare @delay varchar(12)
declare @step_b datetime = getdate()
declare @step_e datetime = getdate()
insert into #set
select top 100 ID from TestMoveFromTable order by ID
while @@rowcount <> 0
begin
set @step_e = getdate()
set @delay = replace(convert(varchar(12), cast((@step_e - @step_b) as time), 113),'.',':')
waitfor delay @delay
set @step_b = getdate()
insert into TestMoveToTable(ID, Value)
select f.ID, f.Value from TestMoveFromTable f
inner join #set s on f.ID = s.ID
set @step_e = getdate()
set @delay = replace(convert(varchar(12), cast((@step_e - @step_b) as time), 113),'.',':')
waitfor delay @delay
set @step_b = getdate()
delete f from TestMoveFromTable f
inner join #set s on f.ID = s.ID
set @step_e = getdate()
set @delay = replace(convert(varchar(12), cast((@step_e - @step_b) as time), 113),'.',':')
waitfor delay @delay
set @step_b = getdate()
truncate table #set
insert into #set
select top 100 ID from TestMoveFromTable order by ID
end
drop table #set
-- move data with auto balancer loop end
select case
when (select count(*) from TestMoveToTable) = @cnt
then 'PASS'
else 'FAIL'
end as TestResult

p ;).