title: Deleting and then insert to target table in a transaction
body: <p>I have the following situation where a stored procedure gathers data and performs the necessary joins and inserts the results into a temp table (ex:#Results)</p>
  
  <p>Now, what I want to do is insert all the records from #Results into a table that was previously created but I first want to remove (truncate/delete) the destination and then insert the results. The catch is putting this process of cleaning the destination table and then inserting the new #Results in a transaction.</p>
  
  <p>I did the following:</p>
  
  <pre><code>BEGIN TRANSACTION
  
  DELETE  FROM PracticeDB.dbo.TransTable
  
  IF @@ERROR &lt;&gt; 0 
      ROLLBACK TRANSACTION
  ELSE 
      BEGIN
  
          INSERT  INTO PracticeDB.dbo.TransTable
                  ( 
                     [R_ID]
                    ,[LASTNAME]
                    ,[FIRSTNAME]
                    ,[DATASOURCE]
                    ,[USER_STATUS]
                    ,[Salary]
                    ,[Neet_Stat]
                  )
                  SELECT  [R_ID]
                        ,[LASTNAME]
                        ,[FIRSTNAME]
                        ,[DATASOURCE]
                        ,[USER_STATUS]
                        ,[Salary]
                        ,[Neet_Stat]
                  FROM    #RESULT
  
          Select @@TRANCOUNT TransactionCount, @@ERROR ErrorCount
          IF @@ERROR &lt;&gt; 0 
              ROLLBACK TRANSACTION
  
          ELSE 
              COMMIT TRANSACTION
  
      END
  </code></pre>
  
  <p>but I know it isn't working properly and I'm having a hard time finding an example like this though I dont know why considering it seems like something common. In this case it still deletes the target table though the insert fails.</p>
  
  <p>More than anything, some guidance would be nice as to best approach this situation or best practices in a similar case (whats best to use and so forth). Thank you in advance...</p>