How to complete an INSERT in batches for large data sets

17 March,2016 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to complete an INSERT in batches for large data sets


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer