title: Is there a way to bulk insert into two tables with FK from one to the other?
body: <p>I'll give a pseudocode example of my current method and if anyone knows of a method that doesn't work one row at a time, I'd be quite appreciative.  I'm using MS SQL Server 2008.</p>
  <pre><code>define cursor for the data to be inserted (about 3 million records)
  loop
  (
      insert record into table 1
      use scope_identity() to get key
      insert record into table 2 that references table 1
  )
  </code></pre>
  <p>I'd much rather do some sort of insert into both tables simultaneously because a cursor and loop are slow.</p>
  <p>Before anyone rants about why I'm inserting something into two separate tables that has a 1 to 1 relationship, it's because the first table cannot be modified and I need the information in the second (temporary) table for reference for data conversion operations later on.</p>
  <p>No, I cannot add a temporary column to hold the reference data on to the table that cannot be modified because it cannot be modified.  This is a live system and I don't have permissions to alter the table.</p>
  <hr />
  <p>Additional Info:</p>
  <p>Ex</p>
  <pre><code>Source:
  
  1 a big  monkey
  2 a tall elephant
  3 a big  giraffe
  4 a tiny cow
  5 a tall cow
  
  Dest:
  
  Table 1       Table 2
  monkey        1 big
  elephant      2 tall
  giraffe       3 big
  cow           4 tiny
  cow           5 tall
  </code></pre>