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 = 2
DECLARE @rowsOffset INT = 0

-- 100000 overall limit per run

IF( OBJECT_ID('tempdb.dbo.#tmpPostIDs') IS NOT NULL ) DROP TABLE #tmpPostIDs
CREATE TABLE #tmpPostIDs ( 
  --RowId INT IDENTITY(1,1),
  RowId INT IDENTITY(100001,1),
  Id INT 
)

--SELECT @rowsOffset = 0
SELECT @rowsOffset =100000

INSERT INTO #tmpPostIDs (Id)
SELECT Id FROM Posts ORDER BY ViewCount DESC OFFSET @rowsOffset ROWS FETCH NEXT 100000 ROWS ONLY;

WHILE( (SELECT COUNT(*) FROM #tmpPostIDs ) > 0 )
BEGIN
  IF( OBJECT_ID('tempdb.dbo.#tmpPostIDsBatch') IS NOT NULL ) DROP TABLE #tmpPostIDsBatch;
  
  SELECT TOP (50000) RowId, Id INTO #tmpPostIDsBatch FROM #tmpPostIDs ORDER BY RowId;
  DELETE FROM #tmpPostIDs WHERE RowId IN ( SELECT RowId FROM #tmpPostIDsBatch)
  
  SELECT tb.RowId, p.Id, p.ViewCount, p.Title, p.Body
  FROM Posts p JOIN #tmpPostIDsBatch tb ON tb.Id = p.Id
  ORDER BY tb.RowId;
END

fork query

created oct 19 daniel.drennan