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 need to 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 lower probability use table you move data from. When you decide to insert data to new table and then delete them of course in delete query you lock whole table. Now imagine that you have 100k rows to delete. Of course nobody can use your operational table because it is locked, right? So here I want to share with you example of how to do that in right way 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 ;).