Q&A for professional and enthusiast programmers
DECLARE @batchId INT = 4 DECLARE @rowOffset INT = 0 DECLARE @batchSize INT = 50000 DECLARE @strSql NVARCHAR(255) SELECT @rowOffset = (@batchSize * (@batchId - 1)) -- 100000 overall limit per run, 50000 oper query but when you attemt to downlaod the 2nd resultsset you get the first -- so as a work-around - running this query 4 times (@batchId 1 through 4) -- it would be desirable to store all 200,000 Id but temp tables are erased between runs -- and you cannot create an actual table IF OBJECT_ID('tempdb.dbo.##tmpPostIDs') IS NOT NULL DROP TABLE ##tmpPostIDs -- seems to be a limitation on re-using tamp tables with the same name so creating it dynamically -- setting the initial RowId for the relative batch start, the rowId identity is ued to keep the sort order SELECT @strSql = N'CREATE TABLE ##tmpPostIDs ( RowId INT IDENTITY( ' + CAST( (@rowOffset + 1) AS NVARCHAR) + N' ,1), Id INT );'; PRINT(@strSql) EXEC(@strSql) BEGIN -- Get ID's in order of ViewCount desc for this batch INSERT INTO ##tmpPostIDs (Id) SELECT Id FROM Posts ORDER BY ViewCount DESC OFFSET @rowOffset ROWS FETCH NEXT @batchSize ROWS ONLY; -- Using the captured ID and generate a result SELECT t.RowId, p.Id, p.Score, p.OwnerUserId, u.DisplayName AS [UserDisplayName], p.ViewCount, p.Title, REPLACE(p.Body, ',',';') AS Body FROM Posts p JOIN ##tmpPostIDs t ON t.Id = p.Id LEFT OUTER JOIN Users u ON u.Id = p.OwnerUserId ORDER BY t.RowId; END; DROP TABLE ##tmpPostIDs