8

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
0

1 Answer 1

12
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:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.