Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
TSQL Batch Loop Snippet
/* 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;
Owner

NJohnson9402 commented Mar 2, 2017

This is primarily for use with RedGate SQLPrompt -- the double-dollar-sign placeholders (tokens), like $BATSZ$ and $LIMIT$, get replaced with values of your choice when you create the snippet in SQLPrompt and then use it in the SSMS editor.

Owner

NJohnson9402 commented Mar 2, 2017

Added finishing message with elapsed time & total row-count.

Owner

NJohnson9402 commented Mar 2, 2017

Added sample usage guidelines in header comments.

Owner

NJohnson9402 commented Aug 31, 2017

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment