Count and last row of a batch(Top) with a insert statement

Question

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)

Answer 1

If I correctly understand you could use @@ROWCOUNT after the INSERT command to get the affected rows:

Given the next example:

CREATE TABLE t1 (v1 int PRIMARY KEY, v2 int);
CREATE TABLE t2 (v1 int, v2 int);
INSERT INTO t1 VALUES
(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

DECLARE @top int = 4,
        @affectedRows int,
        @pointer1 int,
        @pointer2 int;

You can store @ROWCOUNT value:

INSERT INTO t2
SELECT TOP (@top) v1, v2
FROM   t1
--WHERE <CONDITION>
ORDER BY v1, v2;

SET @affectedRows = @@ROWCOUNT;

And then get last row values:

SELECT TOP 1 @pointer1 = v1, @pointer2 = v2
FROM (SELECT TOP (@top) v1, v2
      FROM   t1
      --WHERE <CONDITION>
      ORDER BY v1, v2) T
ORDER BY v1 DESC, v2 DESC;

This is the result:

SELECT @affectedRows Rows, 
       @pointer1 Pointer1, 
       @pointer2 Pointer2;

Rows	Pointer1	Pointer2
4	4	4

db<>fiddle here

Answer 2

If the ability to restart the batch process is important, you might consider this approach.

First, we'll create a sandbox (shamelessly stolen from @McNets excellent answer)

USE tempdb;
SET XACT_ABORT ON;

DROP TABLE IF EXISTS dbo.t1, dbo.t2, #t;

CREATE TABLE dbo.t1
(
      v1        int     NOT NULL
        PRIMARY KEY
        CLUSTERED
    , v2        int     NOT NULL
);

CREATE TABLE dbo.t2
(
      v1        int     NOT NULL
        PRIMARY KEY
        CLUSTERED
    , v2        int     NOT NULL
);

INSERT INTO t1 (v1, v2)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), sc2.id
FROM sys.syscolumns sc1
    CROSS JOIN sys.syscolumns sc2;

On my system, this results in slightly over 1.2 million rows being inserted into the dbo.t1 table.

This is the batch processing piece:

DECLARE @msg nvarchar(1000);
--this makes the process restartable
DECLARE @id  int = COALESCE((SELECT TOP(1) t2.v1 FROM dbo.t2), 0);
WHILE EXISTS (SELECT TOP(1) 1 FROM dbo.t1 t WHERE t.v1 > @id)
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY

        INSERT INTO dbo.t2 (v1, v2)
        SELECT TOP(5000) 
              t1.v1
            , t1.v2
        FROM dbo.t1
        WHERE dbo.t1.v1 > @id;
        
        SET @id = COALESCE((
            SELECT TOP(1) t.v1
            FROM dbo.t2 t
            ORDER BY t.v1 DESC
            ), 0);

        COMMIT TRANSACTION;
        CHECKPOINT;

    END TRY
    BEGIN CATCH
        SET @msg = N'ERROR_NUMBER() = ' + CONVERT(nvarchar(10), ERROR_NUMBER(), 0) 
            + N', ERROR_MESSAGE() = ' + ERROR_MESSAGE();
        ROLLBACK TRANSACTION;
        PRINT @msg;
        BREAK;
    END CATCH
END

On my system, this copies 5,000 rows per batch for a total of 240 batches in 9 seconds. It's restartable and can be scheduled on an on-going basis, and will only copy rows when there are new rows to be copied.