TSQL Batch Loop Snippet

BatchLoopSample.sql

/* NJohnson9402 / natethedba.wordpress.com
Primarily for use with RedGate SQLPrompt Snippet Manager.
The $PLACEHOLDERS$ tokens get replaced with values of your choice when you use the snippet in your SSMS editor.
For plain/raw TSQL use, replace the tokens with actual values as desired.

Typical use-case involves creating a temp-table first, to store your "target table PK values" along with an
Identity value to loop over.  You then fill that temp-table with PK values from your target table, so you
can act on those rows.

For example:
```
CREATE TABLE #IDs (MyTablePK INT PRIMARY KEY CLUSTERED, RowNum INT IDENTITY(1,1));

INSERT INTO #IDs (MyTablePK)
SELECT MyTablePK
FROM MyTable
WHERE SomeColumn = 'OldValue'
  AND SomeOtherColumn = 'Important Condition'
```

Then you'd use both the temp-table and your target table, in your "do work" $QUERIES$, like so:
```
UPDATE MyTable SET MyTable.SomeColumn = 'NewValue'
FROM MyTable
JOIN #IDs
    ON MyTable.MyTablePK = #IDs.MyTablePK
WHERE #IDs.RowNum >= @RowStart
  AND #IDs.RowNum < (@RowStart + @BatchSize)
```

Token explanation:
$BATSZ$ - # of rows (size of operation) per batch
$LIMIT$ - # of total rows to operate on for the entire run
$QUERIES$ - write your important intensive queries here, using the batch variables @RowStart and @BatchSize.
$TABLE$ - affected table to show message about
$ACTIONED$ - what you did to said table (updated, deleted, inserted, etc.)
*/
SET NOCOUNT ON;
DECLARE @Msg nvarchar(MAX) --For printing messages
DECLARE @StartTime datetime, @StartOverall datetime;

DECLARE @RowStart int     --In-loop row#/ID starting value
	, @BatchSize int      --Size per batch
	, @BatchNum int       --Batch counter, to tell us what iteration we're on
	, @Rows int           --Store row-count per iteration, in case we're interested
	, @EndingMax int      --Overall total ending mark, i.e. don't go beyond this row#/ID
	, @TotalRows int      --Total row-count for all iterations
;					    
SELECT @RowStart = 1      --Start at 1 assuming your Identity values start at 1
	, @BatchSize = $BATSZ$  --Do this many rows at a time (per iteration)
	, @BatchNum = 1       --Start batch #-ing at 1 too (unless you like zero-base counters)
	, @Rows = 1           --If using the optional "AND @Rows > 0" loop condition, this ensures a first-run
	, @EndingMax = $LIMIT$  --Don't go beyond this row#/ID
	, @TotalRows = 0
;

--Preamble
SET @StartOverall = GETDATE();
SET @Msg = 'Commencing batch loop operations on $TABLE$, at ' + CONVERT(varchar(30), @StartOverall, 120);
RAISERROR (@Msg, 0, 1) WITH NOWAIT;

DECLARE @SavedStart int = @RowStart; --this is for printing an estimate of "% complete" later

--Loop
WHILE (@RowStart < @EndingMax
	--AND @Rows > 0  /* uncomment this line for optional "row-count check per iteration" */
)
BEGIN
	--Show a starting msg (what batch # & row-range we're about to do)
	SET @Msg = '  Batch #' + CONVERT(varchar(10), @BatchNum) + ', range '
	    + CONVERT(varchar(10), @RowStart) + '-' + CONVERT(varchar(10), @RowStart + @BatchSize - 1)
	RAISERROR (@Msg, 0, 1) WITH NOWAIT;

	--Wrap each iteration in a transaction
	SET @StartTime = GETDATE();
	BEGIN TRAN tInner;

    --DO STUFF HERE!
	$QUERIES$

	SET @Rows = @@ROWCOUNT;
	SET @TotalRows += @Rows;

	COMMIT TRAN tInner;

	--Increment loopy stuff
	SET @RowStart += @BatchSize;
	SET @BatchNum += 1;

	--Print a "did X" msg to show what work was done
	SET @Msg = '    $ACTIONED$ ' + CONVERT(varchar(10), @Rows) + ' rows in $TABLE$, in '
		+ CONVERT(varchar(10), DATEDIFF(MILLISECOND, @StartTime, GETDATE())) + 'ms'
	RAISERROR (@Msg, 0, 1) WITH NOWAIT;
	SET @Msg = '    Total rows so far: ' + CONVERT(varchar(10), @TotalRows) + ' , about '
		+ CONVERT(varchar(10), (CASE WHEN @RowStart < @EndingMax
			THEN (CONVERT(real, @TotalRows) / CONVERT(real, (@EndingMax - @SavedStart)) * 100.0)
			ELSE 100 END)) + ' pct done.';
	RAISERROR (@Msg, 0, 1) WITH NOWAIT;
END;

SET @Msg = 'Total of ' + CONVERT(varchar(10), @TotalRows) + ' done in '
    + CONVERT(varchar(10), DATEDIFF(SECOND, @StartOverall, GETDATE())) + ' seconds.'
RAISERROR (@Msg, 0, 1) WITH NOWAIT;
SET @Msg = 'Finished at ' + CONVERT(varchar(30), GETDATE(), 120);
RAISERROR (@Msg, 0, 1) WITH NOWAIT;

SET NOCOUNT OFF;

Cleaned up formatting, added start-at msg, per-iteration time & percent-done estimate, and finished-at msg/time.