title: Using merge..output to get mapping between source.id and target.id
body: <p>Very simplified, I have two tables Source and Target.</p>
  
  <pre><code>declare @Source table (SourceID int identity(1,2), SourceName varchar(50))
  declare @Target table (TargetID int identity(2,2), TargetName varchar(50))
  
  insert into @Source values ('Row 1'), ('Row 2')
  </code></pre>
  
  <p>I would like to move all rows from <code>@Source</code> to <code>@Target</code> and know the <code>TargetID</code> for each <code>SourceID</code> because there are also the tables <code>SourceChild</code> and <code>TargetChild</code> that needs to be copied as well and I need to add the new <code>TargetID</code> into <code>TargetChild.TargetID</code> FK column. </p>
  
  <p>There are a couple of solutions to this.</p>
  
  <ol>
  <li>Use a while loop or cursors to insert one row (RBAR) to Target at a time and use <code>scope_identity()</code> to fill the FK of <code>TargetChild</code>. </li>
  <li>Add a temp column to <code>@Target</code> and insert <code>SourceID</code>. You can then join that column to fetch the <code>TargetID</code> for the FK in <code>TargetChild</code>.</li>
  <li><code>SET IDENTITY_INSERT OFF</code> for <code>@Target</code> and handle assigning new values yourself. You get a range that you then use in <code>TargetChild.TargetID</code>.</li>
  </ol>
  
  <p>I'm not all that fond of any of them. The one I used so far is cursors.</p>
  
  <p>What I would really like to do is to use the <code>output</code> clause of the insert statement.</p>
  
  <pre><code>insert into @Target(TargetName)
  output inserted.TargetID, S.SourceID
  select SourceName
  from @Source as S
  </code></pre>
  
  <p>But it is not possible</p>
  
  <pre><code>The multi-part identifier "S.SourceID" could not be bound.
  </code></pre>
  
  <p>But it is possible with a merge.</p>
  
  <pre><code>merge @Target as T
  using @Source as S
  on 0=1
  when not matched then
    insert (TargetName) values (SourceName)
  output inserted.TargetID, S.SourceID;
  </code></pre>
  
  <p>Result</p>
  
  <pre><code>TargetID    SourceID
  ----------- -----------
  2           1
  4           3
  </code></pre>
  
  <p>I want to know if you have used this? If you have any thoughts about the solution or see any problems with it? It works fine in simple scenarios but perhaps something ugly could happen when the query plan get really complicated due to a complicated source query. Worst scenario would be that the TargetID/SourceID pairs actually isn't a match. </p>
  
  <p>MSDN has this to say about the <code>from_table_name</code> of the <a href="http://msdn.microsoft.com/en-us/library/ms177564.aspx">output</a> clause.</p>
  
  <blockquote>
    <p>Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.</p>
  </blockquote>
  
  <p>For some reason they don't say "rows to insert, update or delete" only "rows to update or delete".</p>
  
  <p>Any thoughts are welcome and totally different solutions to the original problem is much appreciated. </p>