title: Perform INSERT with SELECT to insert multiple records
body: <p>In the diagram below there is a 1:1 relationship between 'DodgyOldTable' and 'MainTable'. Table 'Option' contains records with 'OptionVal1', 'OptionVal2' and 'OptionVal3' in the 'OptionDesc' field.
  I need to do an insert into MainTable_Option with a select from DodgyOldTable. Something like this:</p>
  
  <pre><code>INSERT MainTable_Option ([MainTableID],[OptionID])
  SELECT ID, (CASE WHEN OptionVal1 = 'y' THEN 
      (SELECT OptionID 
       FROM Option 
       WHERE OptionDesc = 'OptionVal1') END
  FROM DodgyOldTable
  </code></pre>
  
  <p>If possible I want to avoid using several different select statements to perform the insert operation.</p>
  
  <p><a href="http://www.freeimagehosting.net/uploads/863f10bf5f.jpg">alt text http://www.freeimagehosting.net/uploads/863f10bf5f.jpg</a></p>