Pseudo Code for batch processing using WHILE LOOP


/* Note :
	Make sure your table/dataset which you wish to process in batches has the column with running number such as an identity column 
	or if not then dump the data in some local scoped temporary/hash table with column as RowID 
	which can be generated using the ROW_NUMBER() function if you are using INTO command to dump the data. 
	If you are using INSERT INTO... SELECT FROM then you can create RowID as an identity column.

	It is highly recommended to have clustered index on the RowID column
*/

/* Note :
	@Seed represents the starting row number
	@Increment represents the batch count
*/

-- It is advisable to implement the logging here to track when the process was started

DECLARE @From_RowID			INT
	, @To_RowID				INT
	, @Seed					INT	=	1
	, @Increment			INT	=	500
	, @RowID				INT

-- Set @From_RowID & @To_RowID to process the first batch
SET @From_RowID			=	@Seed
SET @To_RowID			=	@Increment

WHILE EXISTS (SELECT 1 FROM table_name WHERE RowID BETWEEN @From_RowID AND @To_RowID)
	BEGIN
		BEGIN TRY
			-- It is advisable to implement the logging here to track the progress so that specific decisions can be taken later.

			/* Data processing, INSERT, UPDATE or DELETE
				Note :
				Make sure to have the filter clause something like this "WHERE RowID BETWEEN @From_RowID AND @To_RowID" 
				so that only specific set of records from the batch should get processed.
			*/

			-- Increment the @From_RowID & @To_RowID to process the next batch
			SET @From_RowID		=	@To_RowID + 1;
			SET @To_RowID		=	@To_RowID + @Increment;
		END TRY
		
		BEGIN CATCH
			-- It is advisable to implement the error logging here to track the progress and failures so that specific decisions can be taken later.

			-- In case any batch got failed due to any specific record in the batch then it's advisable to process that batch again for each individual record so that we can process the suuccessful records and also come to know which record is causing the failure.
			-- Set @RowID to the first Row ID of the batch
			SET @RowID	= @From_RowID
			WHILE EXISTS (SELECT 1 FROM table_name WHERE RowID = @RowID) AND @RowID <= @To_RowID
				BEGIN
					BEGIN TRY
						-- It is advisable to implement the logging here to track the progress so that specific decisions can be taken later.

						/* Data processing, INSERT, UPDATE or DELETE
							Note :
							Make sure to have the filter clause something like this "WHERE RowID = @RowID" 
							so that only specific row from the batch should get processed.
						*/

						SET @RowID			=	@RowID + 1;
					END TRY

					BEGIN CATCH
						-- It is advisable to implement the error logging here to track the progress and failures so that specific decisions can be taken later.

						-- Increment the @RowID to process the next record
						SET @RowID			=	@RowID + 1;
					END CATCH
				END

			SET @From_RowID		=	@To_RowID + 1;
			SET @To_RowID		=	@To_RowID + @Increment;
		END CATCH

-- It is advisable to implement the logging here to track when the process was completed

 

Leveraging WHILE LOOP

One thought on “Pseudo Code for batch processing using WHILE LOOP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s