Problem statement : I've a big table and I read only Top 10000 elements of the table at a time. This is for batch processing(insertion) which has a where clause and one order by statement. Now, in order to proceed for the next batch I need to know the count of current batch and current pointers that is the last row elements of the current batch. If the count < 10000 we can exit the loop.
How can I get the last row elements of the current batch i.e. the 10000th row of the current batch? And how can I get the count of the current Top query?
Note: We might not get 10000 rows for each Top 10000 queries.
This is what I tried. If you look at the query I'm using same query at three different places. temp table might not be a choice here because I think that might increase the overhead. Can you help me optimize the query?
DECLARE @column1Pointer BIGINT = 0
DECLARE @column2Pointer BIGINT = 0
// Inserting the results into a second table
INSERT INTO table2
(
Column1, Column2
)
SELECT TOP(@size), Column1, Column2
FROM table1
WHERE <same condition>
ORDER BY Column1 , Column2
// Fetching the last element of the row. first I use order by asc and then desc and then fetch top 1 row
SELECT TOP 1 @column1Pointer = Column1, @column2Pointer = Column2
FROM
(
SELECT TOP(@size) Column1, Column2
FROM table1
WHERE <same condition>
ORDER BY Column1 , Column2
) T ORDER BY T.Column1 DESC , T.Column2 DESC
OPTION (RECOMPILE)
// third place with same query to get the count of the batch
SELECT @countInBatch = COUNT(*)
FROM
(
SELECT TOP(@size) Column1, Column2
FROM table1
WHERE <same condition>
ORDER BY Column1 , Column2
) T
OPTION (RECOMPILE)