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)