sql-serversqltransaction

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.