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 ;).