From the example code it seems that you want to transfer all outstanding rows, or none. This can be done in a couple of statements, though there are good reasons not to. More on that later.
If there's a foreign key in the detail table referencing the header table that must be honoured or worked around. Either the header table must be populated first, then the detail table, or the foreign key must be disabled, then re-enabled once the data is in place. Either way some data will be read twice.
I'll not use the temporary table (#TotalCID) either, saving the read and write this consumes. Instead I'll create a view on the source table (ConsumerTable). Even though there are a lot more rows in the base table compared to the temporary table (twenty million versus one hundred thousand) you have to read the base table at some point to find the rows to transfer. I believe a filtered index will be useful here. If there are concurrent writes to the base table they can be avoided by using a "high water mark" if there's an identity column or a suitable isolation level. It's OK to retain #TotalCID if you're more comfortable with that approach.
First I will show all the code, then I'll explain my thinking.
drop table if exists ConsumerTable;
drop table if exists ETLDetail;
drop table if exists ETLHeader;
go
create table ConsumerTable
(
ConsumerID varchar(16) primary key, -- Note A
SeedingStatus int default (0) -- Note B
);
go
-- Note C
create index ToTransfer on ConsumerTable (ConsumerID)
where SeedingStatus in (0,2);
go
insert ConsumerTable(ConsumerID)
values ('a'), ('b'), ('c'), ('d'), ('e'),
('f'), ('g'), ('h'), ('i'), ('j');
go
create table.ETLHeader
(
HeaderId int primary key,
Batch_Date date not null,
Total_Rec_count int not null
);
create table ETLDetail
(
Batch_ID int not null,
ConsumerID int not null
);
go
create or alter view ConsumersToTransfer as
with DataRows as
(
select
ConsumerID,
RowId = ROW_NUMBER() over (order by ConsumerID) -- Note D, E
from ConsumerTable
where SeedingStatus in (0, 2) -- Note F
)
select
ConsumerID,
RowId,
BatchId = (RowId - 1) / (select BatchSize = 4) -- Note G
from DataRows;
go
Notes
A For testing I'll make this the PK. It doesn't have to be for this method to work.
B The default is a convenience for my test data. It is not required for this solution.
C I think this filtered index will be useful, but you must have code to find these rows efficiently already. Continue to use that.
D To pre-calculate BatchID I will use integer division. From the docs
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
For that I'll need a unique, gap-less sequence of integers, one for each source row. I'll use ROW_NUMBER() to build this. If your table has an identity column which provides the same conditions it can be used instead. Note that identities can leave gaps on occasion, or rows may have been deleted. If so the batch sizes will not be uniform.
E The OVER() clause is requried. We don't care which rows are together in a batch, however. So use the clustering index's leading column to avoid a sort. If you have a need to keep some rows near others you can change this clause, though there'll likely be a performance hit.
F The predicate matches that of the filtered index defined above.
G I'll use a batch size of 4 for testing. Change this to your production value (25,000) for your tests. I subtract 1 from RowId because ROW_NUMBER() is 1-based and I want the Nth row to end a batch rather than start a new batch.
Here is the code to transfer rows:
begin transaction;
declare @NextBatchStart int
= 1 + (select ISNULL(MAX(HeaderId), 0) from ETLHeader); -- Note H
insert ETLHeader -- Note I
(
HeaderId,
Batch_Date,
Total_Rec_count
)
select
BatchId + @NextBatchStart,
Batch_Date = GETDATE(),
COUNT(*)
from ConsumersToTransfer
group by BatchId;
insert ETLDetail -- Note J
(
Batch_ID,
ConsumerID
)
select
BatchId + @NextBatchStart,
ConsumerId
from ConsumersToTransfer;
update ct
set SeedingStatus = 1
from ConsumerTable as ct
inner join ETLDetail as d -- Note K
on d.ConsumerId = ct.ConsumerId
and d.Batch_ID >= @NextBatchStart -- Note L
where ct.SeedingStatus in (0, 2); -- Note M
commit;
Notes
H This plays the role of your SCOPE_IDENTITY() ensuring new header rows follow on from existing rows.
I Each INSERT will read all source rows. Since this is will be in clustered key sequence (because of the OVER() clause) it should be fast.
J If there are concurrent writes to ConsumerTable these two statements could return different results and, hence, different batches. Specifically Total_Rec_count will be off for the last (non-full) batch.
K An inner join is logically equivalent to your .. IN (select ..).
L Only need to consider batches created in the current run.
M Predicate matches the filtered index.
Wrap this in the error-catching and rollback logic to handle your desired all-or-nothing outcome.
I think part of the reason your current approach takes so long is because the system does not use IX_ConsumerID because the leading column (IsPickedUp) is not selective enough. I touch on this for another question here.
I understand you want all-or-nothing outcomes. It is obvious your hardware can handle this because you have written code that runs. However it takes a toll on the system. All those rows written have to be held in the transaction logs. The logs must grow to accommodate the rows and cannot be truncated until the transaction completes. The system must hold locks, preventing other work from continuing.
A better way may be to process each batch in a transaction. Fewer resources will be needed for each batch compared to the whole. Locks will be released sooner. Restart logic will ensure no row is processed twice or missed.
