title: Recursive Table How Reference To Load Data?
body: <p>I am planning on using a recursive table in my data model so that I can support an undetermined number of children that can live under a parent.  My example is a Table of Contents where I don't know how deep my subsections will be under a chapter. </p>
  
  <p>The issue I am stumbling over is what techniques do folks use to populate there DB once they have defined a recursive table? By that I mean if I have a list of items that refer to a chapter -> Section -> Subsection...when I load the Chapter, Section, and Subsection into the model I need to identity the lowest level of the hierarchy and assign that value to the Item I am loading (Foreign Key - I would assume) so that I can always get all of the info about that item.</p>
  
  <p>So for example:  </p>
  
  <p>Item: 2A-GHI: Chapter: 2  Section: A  SubSection: GHI  </p>
  
  <p>If I have my data loaded like   </p>
  
  <pre><code>ID|TOCID|TOC_VALUE|PARENT_ID  
  1|Chapter|2|-1  
  2|Section|A|1  
  3|SubSection|GHI|2    
  </code></pre>
  
  <p>How do i tie the item to GHI so that I can set the FK to the Recursive Table for that Item?
  Do you use all three values as a Key and set that as another column in the table so that on load you can identity the lowest level?  </p>
  
  <p>Like So:  </p>
  
  <pre><code>ID|TOCID|TOC_VALUE|PARENT_ID|Key  
  1|Chapter|2|-1|2  
  2|Section|A|1|2_A  
  3|SubSection|GHI|2|2_A_GHI
  </code></pre>
  
  <p>I can load the recursive table and I am using a CTE to recurse the data, but I am not sure what the best method is to load the recursive data and tie that data into the model so that my item has a FK to the Table of Contents data.</p>