I have a set of Z entries(eg 100 records), which I want to insert into Header and Detail tables.

I want to divide this group into X groups, with each group containing At-most N entries. The last group may contain <= N entries. Here X is variable and N is static. BatchID is the IDENTITY key of the header table.

1. Simple Approach

a. dump all the data in Temp table
b. Find number of Loops : CEILING(COUNT/N)
c. For every loop insert 1 entry in Header, find IDENTITY KEY and use that as Batch ID and insert N records in Detail Table with that Batch ID like:

    UPDATE TOP(N) #Temp 
    SET IsPickedUp = 1
    OUTPUT @BatchID,deleted., deleted.... 
    INTO Detail(BatchID, ...)
    WHERE IsPickedUp = 0

2. Optimized Approach

   a. Find number of batches to be created of N records, where last Batch Count will be <= N
   b. Insert all records from temp table to Detail table with correct BatchID

I need help for the second approach.

Here is my existing Code that works fine but take 40 min in production. 1 Crucial requirement is that Either the Batches Are Created or it is not Created, we don't want incomplete population(eg. transaction in while loop)

BEGIN TRY
BEGIN TRAN
    CREATE TABLE #TotalCID (ConsumerID VARCHAR(16),IsPickedUp BIT NOT NULL CONSTRAINT DF_IsPickedUp DEFAULT (0))
    CREATE CLUSTERED INDEX  IX_ConsumerID  ON #TotalCID(IsPickedUp,ConsumerID)    

    INSERT INTO #TotalCID (ConsumerID)
    SELECT ConsumerID FROM ConsumerTable WHERE SeedingStatus IN (0,2) -- 2Cr Records
    
    DECLARE @TotalCount DECIMAL(11,2) = 0
    DECLARE @BatchCount DECIMAL(9,2) = 0
    DECLARE @LoopCnt INT = 0
    DECLARE @BatchSize INT = 25000
    DECLARE @batchId INT = 0

    SELECT @TotalCount=COUNT(ConsumerID) FROM #TotalCID

    SET @LoopCnt = CEILING (@TotalCount/@BatchSize) 

    WHILE (@LoopCnt > 0)
    BEGIN
 
        INSERT INTO ETLHeader(Batch_Date,Total_Rec_count)
        VALUES(GETDATE(),0)

        SET @batchId = SCOPE_IDENTITY() -- BatchID in ETLHeader is PK
        
        UPDATE TOP (CONVERT(INT,@BatchSize)) #TotalCID
        SET IsPickedUp=1
        OUTPUT @batchId,deleted.ConsumerID
        INTO ETLDetail(Batch_ID,ConsumerID)   
        WHERE IsPickedUp = 0

        SET @BatchCount = @@rowcount
                     
        UPDATE ETLHeader
        SET  Total_Rec_count = @BatchCount 
        WHERE Batch_Id = @batchId 

        SET @LoopCnt=@LoopCnt-1
    END

        UPDATE ConsumerTable 
        SET SeedingStatus = 1
        WHERE ConsumerID IN (SELECT ConsumerID FROM #TotalCID Where IsPickedUp= 1)          
        AND SeedingStatus IN (0,2)
                        
IF OBJECT_ID('tempdb..#TotalCID') IS NOT NULL
    DROP TABLE #TotalCID
COMMIT TRAN
END TRY 
BEGIN CATCH
-- ERROR and Open TRAN Handling 
END CATCH