title: SQL Insert with data from multiple tables
body: <p>I have four tables: Messages, MessageCategory, MessageStatus and MessageLevel.</p>
  
  <p>MessageCategory, MessageStatus and MessageLevel all just have three fields: Identity (primary key), Code and Description. Messages refers to these three fields and has a few other data fields, including Identity (primary key) MessageText and Order. The Identity fields are auto-incremented fields.</p>
  
  <p>I now need to write an SQL script to add some default data to all four tables. Problem is, I need to create a script which will be sent to a customer who will then execute this script. I cannot write a bit more intelligent code to do the whole update. And while three tables are just simple insert statements, it's the Messages table that causes me some additional headaches.</p>
  
  <p>I cannot remove any indices and I cannot assume it starts counting at 1 for the primary keys.</p>
  
  <p>So, as an example, her's some data:</p>
  
  <pre><code>INSERT INTO MessageCategory (Code) Values ('Cat01');
  INSERT INTO MessageStatus (Code) Values ('Status01');
  INSERT INTO MessageLevel (Code) Values ('Level01');
  </code></pre>
  
  <p>And the messages would need something like this:</p>
  
  <pre><code>INSERT INTO Messages(Category, Status, Level, MessageText, Order) 
  VALUES(
    (SELECT Identity from MessageCategory where Code='Cat01'), 
    (SELECT Identity from MessageStatus where Code='Status01'), 
    (SELECT Identity from MessageLevel where Code='Level01'), 
    'Just some message', 
    1
  );
  </code></pre>
  
  <p>That won't work, though. So, what's the trick to get this working? (Keeping the code readable too...)</p>
  
  <p>Unfortunately, I don't have access to the other database. I can test it but once it seems to work, it's just a matter of send-and-pray-it-works...</p>