2

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?

DECLARE @LoopId INT = 1;

WHILE (@LoopId < 100)
BEGIN
    INSERT INTO Table2
    SELECT Field1 
    FROM Table1
    WHERE ForeignKeyField = @LoopId

    SET @LoopId = @LoopId + 1;
END

1 Answer 1

7

Do all inserts or only the current one rollback if the following loop query was killed in the middle of looping?

If the session has started a transaction before the loop, then all statements will be rolled back. But by default each statement runs in a separate transaction, and only the currently-running INSERT would be rolled back.

4
  • So as long as I don't explicitly start a new transaction outside the loop, then only the currently running INSERT rolls back?
    – J.D.
    Mar 17, 2020 at 20:08
  • 1
    Yes. Although if happen to set SET IMPLICIT_TRANSACTIONS ON a transaction will start with the first insert. But otherwise, yes. Mar 17, 2020 at 21:04
  • Great thanks! Also any idea why I saw kind of the opposite behavior when explicitly surrounding the INSERT statement (inside of the loop) with a BEGIN TRANSACTION and COMMIT? I would've thought this would preserve all previous inserts and only rollback the currently active insert on a KILL, but when I stopped it in the middle of running it instantly stopped and then SSMS told me I had uncommitted transactions when I went to close the tab. When I clicked OK to continue closing the tab, it rolled everything back.
    – J.D.
    Mar 17, 2020 at 22:03
  • When developing in SSMS it's easy to abort a batch and be left with an open transaction. Then the next time you run, you're actually using "nested transactions" instead of top-level transactions. Mar 17, 2020 at 22:28

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.