patternsqlModerate

Breaking a big insert query into smaller ones

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

insertintoquerybreakingbigsmallerones

Problem

I have the below INSERT query. It tries to insert over 50,000 rows and it fails because of logging. Is there a good way of splitting a query like this into batches of about 1000 or so with COMMIT statements so that it is not just one big statement?

INSERT INTO xxx (....) SELECT .... FROM xx

Solution

DECLARE @rc INT = 1;
WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  INSERT dbo.target(cols) 
    SELECT TOP (5000) cols 
    FROM dbo.source AS s
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.target AS t
      WHERE t.key = s.key
    )
    ORDER BY clustering_key;

  SET @rc = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

Also see:

Break large delete operations into chunks

Code Snippets

DECLARE @rc INT = 1;
WHILE @rc > 0
BEGIN
  BEGIN TRANSACTION;

  INSERT dbo.target(cols) 
    SELECT TOP (5000) cols 
    FROM dbo.source AS s
    WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.target AS t
      WHERE t.key = s.key
    )
    ORDER BY clustering_key;

  SET @rc = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

Context

StackExchange Database Administrators Q#86517, answer score: 12

Revisions (0)

No revisions yet.