sql data movement using insert..select then delete the data

Question

hi folks,
I'm working on a data movement script whereby i want to move data from my source database tables to a backup table in another database on same server and then delete this data from the source table.

I'm using the below logic in batches of 50K rows, is there any better logic to do this activity ?

WHILE (@results > 0)   
BEGIN  

	insert into bkp_tables  
	(col1,col2,col3)  
	select top 50000 col1,col2,col3  
	from source_table with (nolock)  
	where date <= '2021-12-31 23:59:59'  
	order by <primary key> asc  
	option (maxdop 4)  
	  
	SET @results = @@ROWCOUNT  

   ;WITH T1  
	AS (SELECT top 50000 * from dbo.source_Table  
	where date <= '2021-12-31 23:59:59'  
	order by <primary key> asc)  

	delete from T1  

END

Answer 1

You should wrap each batch in a transaction.

I would not reselect the items to delete. You should get the items to delete from the target table. Reselecting them to delete creates the opportunity to remove items not inserted.

SET @results = 50000  
WHILE (@results = 50000) -- This prevents the loop from running 1 extra time  
 BEGIN  
 BEGIN TRAN  
  DROP TABLE IF EXISTS #pklist;  
  SELECT TOP (50000)  
 <primary key>  
  INTO #pklist  
     from source_table with (nolock)  
     where date <= '2021-12-31 23:59:59'  
     order by <primary key> asc  
  
 SET @results = @@ROWCOUNT;  
  IF @results > 0  
  BEGIN  
  
  insert into bkp_tables  
  (col1,col2,col3)  
  select  col1,col2,col3  
  from source_table with (nolock)  
  INNER JOIN #pklist ON pk = pk  
          
  delete from source_table  
  INNER JOIN #pklist ON pk = pk  
 END  
 COMMIT  
 END  
  

Answer 2

Check possibility of writing this as a single operation assuming that source table doesn't have any trigger on it:

https://weblogs.sqlteam.com/peterl/2009/04/08/composable-dml/

Answer 3

Is the primary key aligned with the date? For instance, is it an IDENTITY column? Or is it more like a random value like a GUID? In the former case, I think the above is OK, whereas in the latter case it will not efficient, as there has to be a scan or large parts of the index for every batch.

No matter which, Naomi's point about composable DML is worth considering.

Answer 4

As far as I know, I haven't seen a more concise solution than insert into from, try the combined DML linked by naomi. But this seems a bit more cumbersome.

Bert Zhou