title: Select and insert rows into multiple tables , sql server
body: <p>How do I insert multiple rows into two different tables at the same time.</p>
  
  <p>I have 3 tables</p>
  
  <pre><code>create table temp_source
  (
    GroupID varchar(10) primary key,
    ExMsg varchar(20)
  ); 
  
  
  create table temp_exceptions
  (
    ExID int identity primary key,
    ExMsg varchar(20)
  ); 
  
  create table temp_sjobs
  (
    GroupID varchar(10) primary key,  
    ExID int 
  ); 
  </code></pre>
  
  <p><code>temp_sjobs.ExID</code> has a foreign key relationship to <code>temp_exceptions.ExID</code></p>
  
  <p>Now I want to insert all the rows from <code>temp_source</code> table into <code>temp_sjobs</code> and <code>temp_exceptions</code> table at the same time.</p>
  
  <p>The only way I could do it is to loop through each row in <code>temp_source</code> table, insert the row into <code>temp_exceptions</code> table, get the <code>@ExID = scope_identity()</code> and insert that row into the
  <code>temp_sjobs</code> table.</p>
  
  <p>This seems very slow and query takes lot of time because of looping.</p>
  
  <p>Is there a better way to do the insert into multiple tables at the same time.</p>