[SOLVED] SQL Server Partial commit in transaction

SQL Server Partial commit in transaction

I have a transaction and two tables where i am inserting some data, can I do partial commits in SQL server

BEGIN TRANSACTION tran1
  BEGIN  TRY
    --Insert into Table1
    --Insert into Table2
COMMIT TRANSACTION  tran1                           
  END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION tran1 
END CATCH

Above code will rollback both tables data, is there a way we can commit table 1 if there is no error on table 1 insert but rollback table 2 if there is any error occurred.

Solution

The answer is yes, although transactions are indeed atomic you could use a savepoint. So in your case, the code could look like this (untested):

BEGIN  TRY
    BEGIN TRANSACTION
    --Insert into Table1

    -- savepoint
    SAVE TRANSACTION tran1

    --Insert into Table2

    -- commit the whole transaction
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    -- rollback to savepoint
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION tran1 
END CATCH

You may have to adjust insertion order.