SOT: Why do people forget about INSERT/SELECT statements (I-Scream)?

A recent response by Peter Boughton on stackoverflow.com pointed out a commonly overlooked technique for handling multiple selections from a set of database values. Well it reminded me that I wanted to write a small entry on the bizarrely underused INSERT/SELECT method. (Now chances are if you are already familiar with it, you are probably not reading this entry ;) So in summary, a SELECT statement can be used to INSERT multiple records into another table. Frankly, it is so simple I do not know why it is not used more often.

Take this frivolous form as an example. It uses your typical query to generate a series of checkboxes. The checkboxes are given the same name, so the selected id's will be submitted as a comma delimited list. Nothing earth shattering here.


Code

<cfquery name="getFlavours" datasource="#MyDSN#">
SELECT    FlavourID, FlavourName
FROM    IceCreamFlavour
ORDER BY FlavourName
</cfquery>

<form method="post" action="saveFavorites.cfm">
<b>Select your favorite ice cream flavours:</b><br/><br/>
<cfoutput query="getFlavours">
 <input type="checkbox" name="flavourID" value="#FlavourID#">#FlavourName#<br/>
</cfoutput>
<br/>
<input type="submit" name="saveMe" value="Save My Flavours!" />
</form>

Form

Now when it comes time to inserting the selected values into a database, you will often see code using a <cfloop> to INSERT the values one-by-one. While that does work, it is not the most efficient technique because it requires multiple queries. That creates another disadvantage. With multiple queries, there is always the possibility of leaving the database in an inconsistent state if an error occurs. Of course that can be solved with <cftransaction>, but there is a simpler option.

Since the source of the values is a database table, you can use a SELECT statement combined with an IN (...) clause to retrieve the id's selected and INSERT them into another table. All within the same query.

Validation omitted for brevity

<cfquery name="saveData" datasource="#MyDSN#" result="results">
INSERT INTO FavoriteFlavour ( UserID, FlavourID )
SELECT    <cfqueryparam value="#session.userID#"  cfsqltype="cf_sql_integer">,
     FlavourID
FROM    IceCreamFlavour
WHERE    FlavourID IN
     (
     <cfqueryparam value="#form.flavourID#" list cfsqltype="cf_sql_integer">
     )
</cfquery>

As a side benefit, you also get some extra validation. Since a SELECT is used to retrieve the values, only valid record id's will be inserted into the second table. While not the solution for every INSERT, you might be surprised at how flexible and useful this type of INSERT can be.