title: How to do a rollup of one table in SQL Server and have child tables rolled up to refer correctly to the rolled up parent rows in SQL Server
body: <p>We have some tables in our database that pretty much grow infinitely so we are trying to create a task that will rollup old data on a periodic basis (probably daily) and remove the records that are contained in the rollup.  </p>
  
  <p>This is reasonably easy to do just using a GROUP BY.  The catch is that are child tables that need to be rolled up as well and refer to the new completed parent table rows that they refer to and I have no idea how to do that without doing some really lame subqueries.</p>
  
  <p>As an example say I start with the following data in the two tables:</p>
  
  <p>Parent Table (ID is an IDENTITY column)</p>
  
  <pre><code>ID  Type  Amount
  --  ----  ------
   1     1   10.00
   2     1    3.00
   3     3    8.00
   4     3    9.00
  </code></pre>
  
  <p>Child Table</p>
  
  <pre><code>ParentID  Thing  Qty
  --------  -----  ---
         1      8    3
         1      6    6
         2      8    4
         2      5    3
         3      8    2
         3      5    1
         4      5    4
  </code></pre>
  
  <p>First I would roll up the parent table into new rows that are grouped by Type and then the Amounts are summed together which would give the following:</p>
  
  <pre><code>ID  Type  Amount
  --  ----  ------
   5     1   13.00
   6     3   17.00
  </code></pre>
  
  <p>The query to create the above data in Parent the query would look something like the following:</p>
  
  <pre><code>INSERT INTO Parent (Type, Amount)
  SELECT Type, SUM(Amount) AS Amount
  FROM Parent
  GROUP BY Type
  </code></pre>
  
  <p>For the child table I want to do something similar, except grouping by "Thing" and summing Quantity with the ParentID referring to the newly created Parent that corresponds to the original Parent.  This would create something like the following:</p>
  
  <pre><code>ParentID  Thing  Qty
  --------  -----  ---
         5      8    7
         5      5    3
         5      6    6
         6      5    5
         6      8    2
  </code></pre>
  
  <p>This query would look something similar to the following but I don't know how to get the ParentID or how to link to the Parent table:</p>
  
  <pre><code>INSERT INTO Child (ParentID, Thing, Qty)
  SELECT ?, Thing, SUM(Qty) AS Qty
  FROM Child
  INNER JOIN Parent
      ON ? = ?
  GROUP BY ?, Thing
  </code></pre>
  
  <p>I would assume that this is something that happens somewhat frequently so does anyone know what the correct way to handle this situation is?</p>