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

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)
``````

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

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.