title: Insert Into: Returns inserted records
body: <p>I'm trying to insert some new records into a table and return the inserted records.</p>
  
  <pre class="lang-sql prettyprint-override"><code>DECLARE @newRecords TABLE
                      (
                          Id         INT          NULL,
                          Col1       VARCHAR(MAX) NOT NULL,
                          Col2       VARCHAR(MAX) NOT NULL,
                          ForeignKey VARCHAR(MAX) NOT NULL
                      )
  
  INSERT INTO @newRecords
      SELECT ColA AS Col1, ColB AS Col2, Key AS ForeignKey 
      FROM SomeTable;
  
  INSERT INTO PrimaryTable(Col1, Col2)
  OUTPUT inserted* INTO @newRecords
      SELECT Col1, Col2 
      FROM @newRecords;
  </code></pre>
  
  <p>The problem is that outputting the inserted records via the <code>OUTPUT</code> statement causes an error because the column <code>ForeignKey</code> does not exist in <code>PrimaryTable</code>. I am worried about outputting the records to a second temp table because I have no good means of joining the data from <code>PrimaryTable</code> to the <code>@newRecords</code> table.</p>
  
  <p>Ideally what I would like to see happen is: </p>
  
  <ol>
  <li>The new records are populated into the <code>@newRecords</code> temp table from <code>SomeOtherTable</code></li>
  <li>The appropriate columns from the <code>@newRecords</code> temp table are then inserted into the <code>PrimaryTable</code></li>
  <li>Finally the primary keys that were assigned to the newly inserted records in the <code>PrimaryTable</code> are updated into the <code>@newRecords.Id</code> column so that I can go on and do some additional record creations</li>
  </ol>
  
  <p>I've tried messing around with the <code>MERGE</code> statement as well as seeing if I could add some additional statements to the <code>OUTPUT</code> statement but either it's not possible or (more likely) I am not using the syntax correctly.</p>
  
  <p>Any ideas would be greatly appreciated.</p>