How to get SQL Data in chunks from remote server

OFFSET FETCH is a feature added to the ORDER BY clause beginning with the SQL Server 2012 edition. It can be used to extract a specific number of rows starting from a specific index.

Here is a simple example;

SELECT * FROM [dbo].[tblSample] 
order by SampleId 
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

This instruct SQL Server to start from first record and return 10 records only.

Let’s write a dynamic sql to get data in batches; For example we have 7 records from database and we would like to get 3 records on each round trip;

DECLARE @RowCount INT, @RowIncrement INT, @RowsInChunk INT, @RemainingRows INT, @SourceQuery NVARCHAR(255)
DECLARE @RowIncrementValue INT, @BatchValue INT
DECLARE @SQL_Select NVARCHAR(4000);
DECLARE @RowIncrementCheck INT;

SET @RowCount = 7;
SET @RowIncrement = 0;
SET @RowsInChunk = 3;

--initial value
SET @RowIncrementValue = @RowIncrement
SET @BatchValue = @RowsInChunk

--loop through all records
WHILE (@RowIncrement <= @RowCount)
BEGIN

	--prepare SQL statement
	SET @SQL_Select =
	'SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  ' + CAST(@RowIncrementValue AS NVARCHAR) + ' ROWS ' + CHAR(13) +
	'FETCH NEXT ' + CAST(@BatchValue AS NVARCHAR) + ' ROWS ONLY'

	--EXECUTE sp_executesql @SQL_Select
	PRINT @SQL_SELECT

	SET @RowIncrement = @RowIncrement + @RowsInChunk
	SET @RowIncrementValue = @RowIncrement

	--calculate remaining rows
	SET @RemainingRows = @RowCount - @RowIncrement
	PRINT 'Remaining Rows ' +  CAST(@RemainingRows AS NVARCHAR)
	IF (@RemainingRows <= @RowsInChunk) AND (@RowIncrement <= @RowCount)
	BEGIN
		--short circuit this loop
		SET @BatchValue = @RemainingRows
		SET @RowIncrement = @RowCount
	END
END

Here is the result of 3 dynamic sql for 7 records;

SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  0 ROWS 
FETCH NEXT 3 ROWS ONLY
SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  3 ROWS 
FETCH NEXT 3 ROWS ONLY
SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  6 ROWS 
FETCH NEXT 1 ROWS ONLY

In the query above, OFFSET 0 is used to skip 0 rows and FETCH 3 ROWS ONLY is used to extract only 3 rows.

To get additional information about the ORDER BY clause and OFFSET FETCH feature, refer to the official documentation: Using OFFSET and FETCH to limit the rows returned.

Resources

Getting data from remote server

Add to favorites