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
$UNCOMMENT_FOR_ROWCOUNT_CHECK$ - you should default thise to '--' when creating the snippet,
but if you leave it empty, the batch-loop will stop as soon as it attempts an operation that affects 0 rows.
$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, @EndTime 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
;
SELECT @RowStart = 1 --Start at 1 assuming your Identity values start at 1
, @BatchSize = $BATSZ$ --Do $BATSZ$ 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 do any more than $LIMIT$ rows total, i.e. end the loop if we reach this row#/ID
;
SET @StartTime = GETDATE();
WHILE (@RowStart < @EndingMax $UNCOMMENT_FOR_ROWCOUNT_CHECK$ AND @Rows > 0 --optional
)
BEGIN
--Show a starting msg (what batch # & row-range we're about to do)
SET @Msg = 'Batch #' + CAST(@BatchNum AS VARCHAR(10)) + ', range ' + CAST(@RowStart AS VARCHAR(10)) + '-' + CAST(@RowStart + @BatchSize - 1 AS VARCHAR(10))
RAISERROR (@Msg, 0, 1) WITH NOWAIT;
--Wrap each iteration in a transaction
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$ ' + CAST(@Rows AS VARCHAR(10)) + ' rows in $TABLE$'
RAISERROR (@Msg, 0, 1) WITH NOWAIT;
END;
SET @EndTime = GETDATE();
SET @Msg = 'Total of ' + CAST(@TotalRows AS VARCHAR(10)) + ' done in ' + CAST(DATEDIFF(SECOND, @StartTime, @EndTime) AS VARCHAR(10)) + ' seconds.'
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.

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