title: Automatically Create Insert Statements to Fill Junction Table in a Pre-Created DB
body: <p>For a simple android app I'm creating as a teaching tool for myself (for using relational dbs/SQL among other things - pardon the simplicity of the question if you will). I'm pre-creating a sqlite db to ship with the application. I'm doing this based on the following <a href="https://stackoverflow.com/questions/4351195/distribute-db-with-apk">SO question</a>.</p>
  
  <p>I've got two tables with a many to many relationship and a junction table to define those relationships as follows:</p>
  
  <pre><code>CREATE TABLE Names (_id INTEGER PRIMARY KEY,
                      name TEXT
                     );
  
  CREATE TABLE Categories (_id INTEGER PRIMARY KEY,
                           category TEXT
                          );
  
  CREATE TABLE Name_Category (name_id INTEGER,
                              category_id INTEGER,
                              PRIMARY KEY (name_id, category_id),
                              foreign key (name_id) references Names(_id),
                              foreign key (category_id) references Categories(_id) 
                             );
  </code></pre>
  
  <p><br/>
  I've got sets of insert statements to fill the Names and Categories tables. I'm now faced with the task of filling the junction table. I'm sure that I could create the insert statements by hand by looking up the ids of the names and categories that I want to match, but that seems a bit silly.</p>
  
  <p>In order to automatically create the insert statements for the junction table, I imagine that I could create a script based on a set of name and category pairs that will search for the appropriate ids and dump an insert statement. (I came up with this as I was asking the question and will research it. Don't you love it when that happens?)</p>
  
  <p>Does anybody have any suggestions for ways to do this?</p>
  
  <p><b>EDIT</b> I added the foreign keys because, as pointed out below, they'll help maintain integrity between the tables.</p>
  
  <p><b>EDIT #2</b> To solve this, I created a simple Perl script that would take a text file with name - category pairs and dump them out into another file with the appropriate SQL statements.</p>
  
  <p>The name - category text file has a format as follows:</p>
  
  <pre><code>'Name' 'Category'
  </code></pre>
  
  <p><br>
  The Perl script looks like this:</p>
  
  <pre><code>use strict;
  use warnings;
  
  open (my $name_category_pair_file, "&lt;", "name_category.txt") or die "Can't open name_category.txt: $!";
  open (my $output_sql_file, "&gt;", "load_name_category_junction_table.sqlite") or die "Can't open load_name_category_junction_table.sqlite: $!";
  
  while (&lt;$name_category_pair_file&gt;) {
      if (/('[a-zA-Z ]*') ('[a-zA-Z ]*')/) {
          my $sql_statement = "INSERT INTO Name_Category VALUES (
                               (SELECT _id FROM Names WHERE name = $1),
                               (SELECT _id FROM Categories WHERE category = $2))\;\n\n";
  
          print $output_sql_file $sql_statement;
      }
  }
  
  close $name_category_pair_file or die "$name_category_pair_file: $!";
  close $output_sql_file or die "$output_sql_file: $!";
  </code></pre>