/* 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

• Performance Tuning
• Queries
• SQL Server