title: Using SqlBulkCopy in one transaction for multiple, related tables
body: <p>I'm a bit stuck here trying to copy lots of data (a million records total) from three related tables to other three related tables in the same database.</p>
  
  <p>My table design is as follows:</p>
  
  <p><a href="https://i.stack.imgur.com/zl8kl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zl8kl.png" alt="enter image description here"></a></p>
  
  <p>What I need is to be able to copy data from the draft tables over to the non-draft tables in one transaction which I'm able to roll back if anything goes wrong. This is needed because we don't want i.e. <code>Billing</code> and <code>BillingPriceLine</code> records to exist in the database, if the bulk insertion of <code>BillingPriceLineSpecificationDraft</code> copy failed.</p>
  
  <p>However, since I'm using <code>SqlBulkCopy</code> for copying the records, I am not able to get a hold of the new IDs to make the correct relations between the three new tables. If I perform a read on i.e. the <code>Billing</code> table in the transaction to get the correct <code>Billing</code> ID, I get a time out, which is expected since the tables are locked within the transaction.</p>
  
  <p>I have tried setting the <code>IsolationLevel</code> enum on the transaction (in fact, I went crazy and tried them all ;-)), but they didn't do anything it seems.</p>
  
  <p>Is there any good way of doing this that I'm missing?</p>
  
  <p>Thanks in advance.</p>