title: commit every x record in tsql
body: <p>I am coming from oracle background and I am a little confused with SQL Server.</p>
  
  <p>I want to stop SQL Server from auto commit on my insert command. So I am using <code>begin transaction</code>. In the demonstration below I am inserting 100 rows into a test table and I'm going to rollback at end of the transaction. The result that I am expecting is 90 rows (commit should be at 30, 60 and 90 rows) and rest 10 record should rollback.</p>
  
  <pre><code>CREATE TABLE test (TEST_ID INT);
  GO
  
  DECLARE @cnt INT=0;
  BEGIN TRANSACTION t1;
  
  WHILE @cnt &lt; 100
  BEGIN
    INSERT INTO test values (@cnt); 
    SET @cnt += 1;
  
    if (@cnt %30 = 0)
      print 'commit '+cast(@cnt as varchar(100))
      COMMIT TRANSACTION t1;
  END
  
  rollback transaction t1;
  GO
  </code></pre>
  
  <p>First I made sure my SQL Server Management Studio auto commit is turned off, for that I follow these steps:</p>
  
  <pre><code>Tools &gt; Option &gt; Query Execution &gt; Sql Server &gt; Ansi &gt; checked the SET IMPLICIT_TRANSACTION
  </code></pre>
  
  <p>after that I ran the script but I am getting wrong result and some error here and there. </p>
  
  <blockquote>
    <p>The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.</p>
  </blockquote>
  
  <p>and the number of rows is 100!</p>
  
  <p>I tried to close my connection and open it again bcs in oracle when I was inserting let say 100 rows and not commit the last let say 10 rows, others could see 90 rows, but me was seeing 100 rows, because it was already saved in my session. But it looks like SQL Server behaves differently with session and transaction.</p>
  
  <p>Another problem that I faced was about the commit inside the while loop, look like it doesn't have access to the begin transaction out of the loop. when I am running the commands, I am not selecting anything. just pressing f5. Even I tried select commands and run but still it gave me error about:</p>
  
  <blockquote>
    <p>The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.</p>
  </blockquote>
  
  <p>I am getting crazy with SQL Server, I can work very easy with mysql and oracle, but SQL Server ....</p>