Outline to page through MSSQL inserts (especially helpful for big joins)

loop_inserts.sql

/* Drop temporary table if exists */
DROP TABLE IF EXISTS #CommTestefd5adf666914ec88906066cdcf4de1f

/* Create a temporary table */
CREATE TABLE #CommTestefd5adf666914ec88906066cdcf4de1f
(
	[Id] [int] NOT NULL,
	[Urn] [sql_variant] NULL,
	[StepId] [int] NULL,
	[StateId] [int] NULL,
 )

/* DECLARATION OF VARIABLES */
DECLARE @IterationLoopTable table (ID int)
DECLARE @IterationLoop int
DECLARE @TotalLoop int
DECLARE @PageNumber AS INT
DECLARE @RowspPage AS INT

/* SET THE START VARIABLES */ 
SET @PageNumber = 1
SET @RowspPage = 10
SET @TotalLoop = 0

/* BEGIN LOOP */
WHILE ( @IterationLoop > 0 OR @IterationLoop IS NULL)
BEGIN 

	/* EMPTY THE VARIABLE FOR THE ITERATION */
	DELETE FROM @IterationLoopTable
	SET @IterationLoop = 0
	
    /* INSERT VALUES INTO TEMP TABLE */
	INSERT INTO #CommTestefd5adf666914ec88906066cdcf4de1f (
	  [Id]
	 ,[Urn]
	 ,[StepId]
	 ,[StateId]
	 )
	/* OUTPUT RESULT IDs in TABLE VARIABLE */
	OUTPUT INSERTED.Id INTO @IterationLoopTable
	/* SELECT FOR THE INSERT */
    SELECT [Id]
	 ,[Urn]
	 ,[StepId]
	 ,[StateId] FROM [dbo].[Communications] WHERE id <= 2000 ORDER BY Id OFFSET((@PageNumber - 1) * @RowspPage) ROWS
	FETCH NEXT @RowspPage ROWS ONLY


	/* PUT NO OF ROWS FOR THIS ITERATION IN TOTAL COUNT */
	SET @IterationLoop = ( select count(*) from @IterationLoopTable )
	SET @TotalLoop = @TotalLoop + @IterationLoop
	SET @PageNumber = @PageNumber + 1

END
/* END LOOP */

/* CHECK FILLED TABLE */
SELECT * FROM #CommTestefd5adf666914ec88906066cdcf4de1f

/* CHECK VARIABLES */
SELECT @IterationLoop
SELECT @TotalLoop
SELECT @PageNumber