If I kill a looping INSERT query, will it rollback all inserts since the loop started or only the last iteration? 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 &lt; 100)
BEGIN
    INSERT INTO Table2
    SELECT Field1 
    FROM Table1
    WHERE ForeignKeyField = @LoopId

    SET @LoopId = @LoopId + 1;
END
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.