0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited oct 19

created oct 19 daniel.drennan