title: SQL - Inserting records into multiple tables, and Scope_Identity
body: <p>I have a temporary table derived from a number of tables, which needs to be inserted into two tables (T1 and T2). T1 has a primary key (auto generated), which must be inserted into T2 as a foreign key (One : Many relationship)</p>
  
  <p>I know if I used the following insert statement</p>
  
  <pre><code>INSERT INTO T1 (.....)
  SELECT (.....) FROM X
  </code></pre>
  
  <p>I am unable to use the scope_identity, as that would only give the me last auto generated ID, to use in T2. </p>
  
  <p>Other than either using a curser or looping though each row, what options exist, for ensuring the relationships between records split across tables remain? Just for information, this insert process happens regularly, and could included up 1000+ records across the two tables.</p>