title: SQL how to rollback transaction if bulk insert fails
body: <p>I've written a query to delete all rows from a table and then bulk insert from a txt. If there is a problem with the bulk insert  I don't want to loose my old data so I want to rollback the transaction.</p>
  
  <p>This is what I've been trying:</p>
  
  <pre><code>BEGIN TRANSACTION
  DELETE Users_Login
  BULK INSERT Users_Login FROM '\\STRMV1234\ - Some_Table.txt'  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2, CODEPAGE = 'ACP')
  COMMIT TRANSACTION 
  </code></pre>
  
  <p>However if there is an error I'm losing everything from the table. I also tried the code below, which doesn't work either:</p>
  
  <pre><code>BEGIN TRY
      BEGIN TRANSACTION 
          DELETE Users_Login
          BULK INSERT Users_Login FROM '\\STRMV1234\ - Some_Table.txt'  WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2, CODEPAGE = 'ACP')
      COMMIT
  END TRY
  BEGIN CATCH
      IF @@TRANCOUNT &gt; 0
          ROLLBACK
  END CATCH
  </code></pre>