title: Move data from staging tables to live tables while maintaining one-to-many relationship
body: <p>I have a database structure that has two one-to-many relationships. I have a website, which has many subjects, each of which has many questions. This results in three database tables, one for each of these types of data.</p>
  
  <p>What I am in the process of doing is implementing a staging area - somewhere where I can make changes to these websites without affecting the live site. So, I've duplicated the table structure with the intent of editing the duplicates, and pushing changes to the live version when the changes are ready. I would like to do this push in one stored procedure, but I can't figure out how to maintain the relationship between the subjects and their questions.</p>
  
  <p>The website has identifying features other than its ID, so it's easy enough to find the live version of the site, and update its information, and even to transfer that website's subjects from the staging tables to the live tables.</p>
  
  <p>Here is some pseudocode for what my plan is for the push:</p>
  
  <pre><code>-- pass in the ID of the staging website
  -- get the ID of the live website
  -- update live website data from staging website data
      -- this is a strict update - if there is a staging version, there will necessarily be a live version
  -- delete all live subjects
  -- delete all live questions
  -- copy subjects from staging to live
  -- copy questions from staging to live
  </code></pre>
  
  <p>Note that subjects and questions may be added or removed, in addition to being edited (thus, deleting and reinserting seems the best course of action).</p>
  
  <p>I've been able to write actual code for most of this, as it's relatively simple. But figuring out how to maintain the relationship between the subjects and the questions has been a problem. If I were doing this in a server-side scripting language, I would do something like this (again, pseudocode):</p>
  
  <pre><code>for each subject
      copy information from staging subject to live subject
      get id of new live subject
      for each question in this subject
          copy information from staging to live, setting subject ID to new live ID
  </code></pre>
  
  <p>As I said, I'd like to keep this all in the same stored procedure. If this is not possible, then I'll obviously go for the above version, but for efficiency's sake, I'd rather not have several database hits.</p>