Wednesday, July 8, 2009

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.

6 comments:

Anonymous,  July 9, 2009 at 3:50 AM  

Q: Why do people forget aboutINSERT/SELECT statements?

A: Ruby on Rails

Anonymous,  July 9, 2009 at 7:28 AM  

Just watch out for no items checked, that will throw an error.

FIGCEO July 9, 2009 at 8:30 AM  

A very similar example is covered in the official Adobe Advanced ColdFusion 8 instructor-led courseware

cfSearching July 9, 2009 at 9:08 AM  

@Anonymous,

True. I omitted validation for brevity.

@FIGCEO,

It sounds like more people should take that course then. (Though I hope it does not also use "ice cream" flavours .. because that would be too weird ;).

-Leigh

r937 July 11, 2009 at 6:52 PM  

excellent technique, very nicely illustrated

good job!

cfSearching July 11, 2009 at 8:53 PM  

@r937,

Thank you :)

-Leigh

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep