Create dynamic table for offset and fetch next using SQL?-CodePudding

I have one table name mytable which has row count 5300 (it should vary 2000, 3300 so on) and one IDENTITY columns: ID:

Case one: if mytable has 4500 rows and output should be like below.

off	fetch
0	1000
1001	2000
2001	3000
3001	4000
4001	4500

Case two: if mytable has 2450 rows and output should be like below.

off	fetch
0	1000
1001	2000
2001	2450

and so on. The difference between off and fetch is 1000.

CodePudding user response：

solution:-

DECLARE @ROWSCOUNT decimal(10,0),
        @NTILES INT;
SET @ROWSCOUNT = (SELECT COUNT(1) FROM [dbo].[tblLookupIncrementalLoad])
SET @NTILES = CEILING(@ROWSCOUNT/1000)

select a.concurrentPipelinecount, MIN(a.ID) AS offSetNum, max(a.ID) AS fetchNum, COUNT(concurrentPipelinecount) AS tableCount from 
(select *, NTILE (@NTILES) OVER (ORDER BY id) concurrentPipelinecount FROM [dbo].[tblLookupIncrementalLoad] where SkipFlag = 0) as a
GROUP by a.concurrentPipelinecount