title: Replace data in column with foreign key to same data in other table
body: <p>Hopefully you guys can help me figure out how to do something I haven't come across before.</p>
  
  <p>I have a table, call it TableA, with many columns. One of them is 'state', with entries such as:</p>
  
  <pre><code>state
  'MA'
  'NJ'
  'HI'
  </code></pre>
  
  <p>and so forth. I would like to create some sort of way to pull these values out into a new foreign-keyed table, call it StateTable, which would have columns state_key and state. </p>
  
  <p>For example, here's how I envision it proceeding row-by-row:</p>
  
  <p>Row 1: Value in 'state' column in TableA is 'MA'. Check to see if StateTable has an entry where the 'state' column is 'MA'. If so, get the state_key for that row, and replace the entry in TableA with the foreign key, so that row now has a FK to 'MA' instead of storing that value directly. If StateTable has no 'MA' entry, insert it, and do the same with the new FK. And so on, for each row.</p>
  
  <p>So the end result would be two tables:</p>
  
  <p>TableA</p>
  
  <pre><code>state
  1
  2
  3
  </code></pre>
  
  <p>StateTable</p>
  
  <pre><code>state_key    state
  1            'MA'
  2            'NJ'
  3            'HI'
  </code></pre>
  
  <p>There shouldn't be any hard-coded stuffs going on because I'll need to do this for other columns as well, like state, that have a somewhat small number of finite values.</p>
  
  <p>tl;dr a way to preserve data in a column while turning the column into a FK'd table.</p>
  
  <p>Any ideas? Thanks!!</p>