title: How to prevent a specific INSERT statement from rolling back in a transaction
body: <p>I have a very huge SP and I have a transaction in it. I am running an algorithm in the SP and if the algorithm doesn't succeed, the transaction gets rolled back.</p>
  <p>I need to log some data even if the transaction gets rolled back, but when the transaction is rolled back, it also rolls back the logs as well. This is a normal behavior, but I need to exclude those log insert statements from the the rollback, so the transaction still gets logged.</p>
  <p>I have a temp table called <code>#MissingAllocationLines</code>, and I insert my logs into that table. Then if it rollbacks, I need to insert all rows from <code>#MissingAllocationLines</code> into a real table called <code>DLWMS_ALLOCATIONMISSINGLOG</code></p>
  <p>Is that possible? My sample code is below</p>
  <pre><code>create table #MissingAllocationLines
  (ALLOCATIONJOBID BIGINT,
  ORDERID BIGINT,
  ORDERDETAILID BIGINT,
  ITEMID BIGINT,
  STOCKQUANTITY BIGINT,   
  ORDERQUANTITY BIGINT)
  
  
  BEGIN TRANSACTION
  
  WHILE(.....)
  BEGIN
      INSERT INTO #MissingAllocationLines (ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY)
      VALUES (@ALLOCATIONJOBID,@OrderID,@OrderDetailID,@ItemID,ISNULL(@StockFreeQuantity, 0),ISNULL(@RemainingQuantity,0))
      ...
      ...
      ...
  END
  
  IF(@DONE=1)
  BEGIN
      COMMIT TRANSACTION
  END
  ELSE
  BEGIN
      ROLLBACK TRANSCATION
  
      INSERT INTO DLWMS_ALLOCATIONMISSINGLOG (ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY)
      SELECT ALLOCATIONJOBID,ORDERID,ORDERDETAILID,ITEMID,STOCKQUANTITY,ORDERQUANTITY
      FROM #MissingAllocationLines
  END 
  </code></pre>