title: Is it possible to for SQL Output clause to return a column not being inserted?
body: <p>I've made some modifications to my database and I need to migrate the old data to the new tables. For that, I need to fill a table (ReportOptions) taking the data from the original table (Practice), and fill a second intermediate table (PracticeReportOption).</p>
  
  <pre><code>ReportOption (ReportOptionId int PK, field1, field2...)
  Practice (PracticeId int PK, field1, field2...)
  PracticeReportOption (PracticeReportOptionId int PK, PracticeId int FK, ReportOptionId int FK, field1, field2...)
  </code></pre>
  
  <p>I made a query to get all the data I need to move from Practice to ReportOptions, but I'm having trouble to fill the intermediate table</p>
  
  <pre><code>--Auxiliary tables
  DECLARE @ReportOption TABLE (PracticeId int /*This field is not on the actual ReportOption table*/, field1, field2...)
  DECLARE @PracticeReportOption TABLE (PracticeId int, ReportOptionId int, field1, field2)
  
  --First I get all the data I need to move
  INSERT INTO @ReportOption
  SELECT P.practiceId, field1, field2...
    FROM Practice P
  
  --I insert it into the new table, but somehow I need to have the repation PracticeId / ReportOptionId
  INSERT INTO ReportOption (field1, field2...)
  OUTPUT @ReportOption.PracticeId, --&gt; this is the field I don't know how to get
         inserted.ReportOptionId
    INTO @PracticeReportOption (PracticeId, ReportOptionId)
  SELECT field1, field2
    FROM @ReportOption
  
  --This would insert the relationship, If I knew how to get it!
  INSERT INTO @PracticeReportOption (PracticeId, ReportOptionId)
  SELECT PracticeId, ReportOptionId
    FROM @ReportOption
  </code></pre>
  
  <p>If I could reference a field that is not on the destination table on the OUTPUT clause, that would be great (I think I can't, but I don't know for sure). Any ideas on how to accomplish my need?</p>