Breaking a big insert query into smaller ones 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
DECLARE @rc INT = 1;
WHILE @rc &gt; 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