SQL Server – Effect of Killing a Looping INSERT Query

insertrollbacksql serversql-server-2016transaction

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

Best Answer

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.

Related Question