title: How to use BEGIN TRANSACTION with while loop in SQL Server?
body: <p>How to use <code>BEGIN TRANSACTION</code> with while loop in SQL Server?</p>
  
  <p>This query never finishes perhaps because it stops and look for <code>COMMIT TRANSACTION</code> after inserting one row (when <code>@cnt = 1</code>) but I don't want to <code>COMMIT TRANSACTION</code> because I want to see results before committing.</p>
  
  <pre><code>BEGIN TRANSACTION 
    DECLARE @cnt INT = 0;
  
    WHILE @cnt &lt;= 100
    BEGIN
        DECLARE @offset INT = 1
  
        INSERT INTO totalSales (col1, col2)
            SELECT 
                'Col1', ROW_NUMBER() OVER (ORDER BY col2) + @offset 
            FROM
                sales
  
        SET @cnt = @cnt + 1;
    END;
  </code></pre>
  
  <p>So how I can check result before commit in while loop?</p>