Skip to content

Instantly share code, notes, and snippets.

Embed
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;
@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

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.

@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

commented Mar 2, 2017

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

@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

commented Mar 2, 2017

Added sample usage guidelines in header comments.

@NJohnson9402

This comment has been minimized.

Copy link
Owner Author

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
You can’t perform that action at this time.