How to complete an INSERT in batches for large data sets

Question : I have a very large recordset with 144 million rows. I’d like to INSERT the recordset into an existing table. Running the INSERT ..SELECT creates a transaction log file beyond the limits of the disk available. Is there a simple method to INSERT the rows.

Answer: Here is a basic pattern you can use to generate an INSERT statement with batches based on a SELECT statement. The loop iterates through until all the rows are INSERTED.

If you’ve changed your database to SIMPLE RECOVERY , then the transaction log will be continuously flushed. The problem of the transaction log size growth disappears.

Your solution will depend on the nature of the BULK INSERT. You may need to play around with the batch sizes.

The problem you may encounter is as the target table recordset grows the NOT EXISTS part may become slow. This may require a Non Clustered Index

use MyDB
GO
DECLARE @BatchSize INT = 5000
WHILE 1 = 1 
BEGIN

     INSERT INTO temp_GroupEmails WITH (TABLOCK) (emailid_id, email_class_id) 
     SELECT TOP (@BatchSize) me.email_id, dv.email_class_id
     from myEmails me 
     WHERE 
     me.email_class_id = 'ysquwy777'
     AND
     NOT EXISTS 
      (SELECT 1 FROM temp_GroupEmails WHERE email_id = me.email_id)

     IF @@ROWCOUNT < @BatchSize BREAK

END