commit every x record in tsql

Question

I am coming from oracle background and I am a little confused with SQL Server.

I want to stop SQL Server from auto commit on my insert command. So I am using begin transaction. 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.

CREATE TABLE test (TEST_ID INT);
GO

DECLARE @cnt INT=0;
BEGIN TRANSACTION t1;

WHILE @cnt < 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

First I made sure my SQL Server Management Studio auto commit is turned off, for that I follow these steps:

Tools > Option > Query Execution > Sql Server > Ansi > checked the SET IMPLICIT_TRANSACTION

after that I ran the script but I am getting wrong result and some error here and there.

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

and the number of rows is 100!

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.

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:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

I am getting crazy with SQL Server, I can work very easy with mysql and oracle, but SQL Server ....

Answer 1

Actually Sql Server doesn't support nested transactions. It will allow you to begin nested transaction but will not allow to rollback here and there. Only way to achieve this is to use save transaction statement. You can add some save points to transaction in loop and then outside loop you can rollback to that savepoint and commit transaction. Here is an example:

DECLARE @cnt INT=0
Begin transaction

WHILE @cnt<100
BEGIN
  INSERT INTO test values (@cnt)
  SET @cnt+=1

  if (@cnt%30=0)
  begin
      save transaction t1
  end
END;

rollback transaction t1;
commit;

Select * from test

Output is from 0 to 89.

Here is fiddle http://sqlfiddle.com/#!6/07af4/37