title: MySQL procedure to load data from staging table to other tables. Need to split up multivalue field in the process
body: <p>I'm trying to export data from a multivalue database (Unidata) into MySQL. Lets say my source data was a person's ID number, their first name and all the states they've lived in. The states field is a multi value field and I'm exporting them so that the different values within that field are seperated by a ~. A sample extract looks like:</p>
  
  <pre><code>"1234","Sally","NY~NJ~CT"
  "1235","Dave","ME~MA~FL"
  "3245","Fred","UT~CA"
  "2344","Sue","OR"
  </code></pre>
  
  <p>I've loaded this data into a staging table</p>
  
  <pre><code>Table:staging
  Column 1: personId
  Column 2: name
  Column 3: states
  </code></pre>
  
  <p>What I want to do is split this data out into two tables using a procedure: a persons table and a states table. A person can have many entries in the states table:</p>
  
  <pre><code>Table 1: persons
  Column 1: id
  Column 2: name
  
  Table 2: states
  Column 1: personId
  Column 2: state
  </code></pre>
  
  <p>My procedure takes the data from the staging table and dumps it over to table 1 just fine. However, i'm a little lost how how to split the data up and send it to table 2. Sally would need to have three entries in the states table (NY, NJ, CT), Dave would have 3, Fred would have 2 and Sue would have1 (OR). Any ideas on how to accomplish this?</p>