A good friend of mine, and fellow CFer, came to me with an interesting situation yesterday. She was trying to insert the contents of a query resultset into a database table. To do this she tried to use a query of queries, which failed with the message "Query Of Queries syntax error. Encountered "INSERT.[sic]" Exact code changed to protect the innocent:

view plain print about
1<cfquery name="update" dbtype="query">
2    INSERT INTO article_info (id, title)
3    SELECT id, title
4    FROM articles_query
where article_query is a dynamically created query object and article_info is a physical database table.

One aspect of the problem is that query of queries does not support SQL statements other than SELECT. The ColdFusion docs make this point, but not explicitly, when defining query of queries:

A query that retrieves data from a record set is called a Query of Queries. After you generate a record set, you can interact with its results as if they were database tables by using Query of Queries. Livedocs - ColdFusion 8 - Using Query of Queries

Another part of the problem is the dbtype="query" attribute in the query. This tells ColdFusion that we are working with a query of queries. Since article_info is not a query object, CF will not find it in memory.

The solution we worked out is to loop over the query object and build the single INSERT statement she wanted using a series of SELECT...UNION ALL statements:

view plain print about
1<cfquery name="update" datasource="#dsn#">
2    INSERT INTO article_info (id, title)
3    <cfoutput query="articles_query">
4        SELECT #id#, '#title#'
5        <cfif currentrow LT article_info.recordcount>UNION ALL</cfif>
6    </cfoutput>

The result of the output loop creates an INSERT statement that looks like:

view plain print about
1INSERT INTO article_info (id, title)
2SELECT 1, 'Article One' UNION ALL
3SELECT 1, 'Article Two' UNION ALL
5SELECT 10, 'Article Ten'

I understand on the surface why query of queries only support SELECT queries. A query object does not always originate from a database - tags such as cfdirectory and cfsearch create query objects. I'm still curious, however, about the inner workings of query of queries. Is it possible in theory to allow an INSERT or DELETE statement?