- May 19, 2021
- 1 min read
The below code can help you load data in batches in a table
DECLARE @INCREMENTVALUE INT = 1,@ROWSINBATCH INT = 100,@RESULT INT
WHILE(@INCREMENTVALUE > 0)
BEGIN
SELECT *
FROM ADVENTUREWORKS2019.PERSON.PERSON
ORDER BY BUSINESSENTITYID
OFFSET @INCREMENTVALUE-1 ROWS
FETCH NEXT @ROWSINBATCH ROWS ONLY
SET @RESULT = @@ROWCOUNT
SET @INCREMENTVALUE = @INCREMENTVALUE + @RESULT
END
where @ROWSINBATCH should be supplied with the batch that you want to process like 100, 1000 etc
@INCREMENTVALUE should be the starting value from where to start processing each time
here it is 1 for 1-100, 101-200 etc
Dont forget the @RESULT to get the number of rows affected and put it back to @INCREMENTVALUE to increment your batch.