title: How to correlate inserted IDs with selected source row IDs?
body: <p>I'm trying to perform a somewhat complex copy operation for a series of referential rows in a set of poorly-normalized hierarchical tables. Unfortunately, these tables use surrogate keys, but there are instances in the hierarchy where some tables have a surrogate key but no valid uniqueness properties within the table. Therefore, as I insert my copied rows in each hierarchical table, there are cases when I cannot avoid duplicate records because there is no way for me to correctly differentiate what are effectively duplicate rows. Sadly, I cannot change the structure of this database.</p>
  
  <p>The technique I'm using is to INSERT based on the results of a SELECT statement, where each row in the select joins together rows that form a hierarchy of source entities; from this join result, copied content is taken and inserted into rows that represent the destination hierarchy. In the join statement, the surrogate key ids are used. For example:</p>
  
  <pre><code>INSERT INTO [PersonTable]
  SELECT [sourcePerson].[Content], [destParent].[Id]
  FROM [GrandParentTable] [sourceGrandParent]
  JOIN [ParentTable] [sourceParent] ON [sourceParent].[GrandParentId] = [sourceGrandParent].[Id]
  JOIN [PersonTable] [sourcePerson] ON [sourcePerson].[ParentId] = [sourceParent].[Id]
  JOIN [GrandParentTable] [destGrandParent] ON [destGrandParent].[Name] = 'CopyTo'
  JOIN [ParentTable] [destParent] ON [destParent].[GrandParentId] = [destGrandParent].[Id]
  WHERE [sourceGrandParent].[Name] = 'CopyFrom'
  </code></pre>
  
  <p>My problem is that this hypothetical table, [PersonTable], does not have sufficient columns or constraints to uniquely identify rows within it, so if I wanted to subsequently copy rows from another table [ChildTable], I would get duplicate rows when executing a subsequent query:</p>
  
  <pre><code>INSERT INTO [ChildTable]
  SELECT [sourceChild].[Content], [destPerson].[Id]
  FROM [GrandParentTable] [sourceGrandParent]
  JOIN [ParentTable] [sourceParent] ON [sourceParent].[GrandParentId] = [sourceGrandParent].[Id]
  JOIN [PersonTable] [sourcePerson] ON [sourcePerson].[ParentId] = [sourceParent].[Id]
  JOIN [ChildTable] [sourceChild] ON [sourceChild].[PersonId] = [sourcePerson].[Id]
  JOIN [GrandParentTable] [destGrandParent] ON [destGrandParent].[Name] = 'CopyTo'
  JOIN [ParentTable] [destParent] ON [destParent].[GrandParentId] = [destGrandParent].[Id]
  JOIN [PersonTable] [destPerson] ON [destPerson].[ParentId] = [destParent].[Id]
  WHERE [sourceGrandParent].[Name] = 'CopyFrom'
  </code></pre>
  
  <p>One way I had hoped to get around this was to somehow correlate the copied row ids with the source Ids, and store them in temp tables that could replace these joins. I wanted to use the OUTPUT clause for this, so that I could grab the inserted row's [Id] value, but even though it seems sensible that you should be able to output anything from the joined tables, this isn't the case.</p>
  
  <p>Assume for this next example that there already exists a table that we've filled with valid ID pairs:</p>
  
  <pre><code>CREATE TABLE #tempParentIds
  (
      [destParentId] int
      [sourceParentId] int
  )
  </code></pre>
  
  <p>We attempt to fill the next level down in the hierarchy with the following statement:</p>
  
  <pre><code>CREATE TABLE #tempPersonIds
  (
      [destPersonId] int
      [sourcePersonId] int
  )
  INSERT INTO [PersonTable] ([Content], [ParentId])
  OUTPUT INSERTED.[Id], [sourcePerson].[Id] INTO #tempPersonIds
  SELECT [sourcePerson].[Content], [destPerson].[ParentId]
  FROM #tempParentIds [tempParent]
  JOIN [PersonTable] [sourcePerson] ON [sourcePerson].[ParentID] = [tempParent].[sourceParentId]
  JOIN [PersonTable] [destPerson] ON [destPerson].[ParentId] = [tempParent].[destParentId]
  </code></pre>
  
  <p>However, this statement will not execute because [sourcePerson] isn't accessible in the OUTPUT clause, and thus we have no way of keeping track of the source row and the destination row that was copied from it.</p>
  
  <p>Is there a way to obtain information from an inserted row in addition to information from outside the inserted row's columns? If this is possible I'd love to know how, but if I'm going about this the wrong way based on the description of my problem please feel free to set me straight, too.</p>