Selecting CF Query of Queries results into a temp table???

If you create a new table that matches the QoQ, You should be able to do something like

INSERT INTO tempTable
SELECT * FROM QoQTable

if the column names and counts dont match (including data types) then you'll have to spell out all the column names

INSERT INTO temptable (col1, col2, col3)
SELECT col1, col2, col3
FROM QoQTable

You need to provide a bit more information.

1. The term "temp table" has several meanings. Are you referring to a literal "temp table" as in an MS SQL #tempTable?
2. Ultimately, what do you need to do with this "temp table" of information?

A QoQ only exists in memory, and AFAIK _cannot_ be transferred directly to a _physical_ database table with an INSERT statement like the one mentioned above. It would require looping.

If you could provide more details, we could assist you with a solution.

erikTsomik

Hi agx, can you get back to my issue as well

Hi Erik,

I just did. Let me know on the other thread if you have any questions.

ASKER

Woa, thanks everyone, I turned my back for two mins...

OK, here's what I'm trying to do... the QofQs is the result of a search against a collection of documents on a server.

The result set includes the "Key" which is for example: 999.doc
I have a database of info associated with the key (i.e. ContentID 999)
I'm hoping to copy all of the results of the QofQs into a #temp table
Remove the file ext from the key (999) to join to my content table where I can retrieve more info regarding the document.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_ is correct saying that you would have to loop it... but.. Do this...

<cfquery>
--LOOP OVER QoQ Results
<cfloop query="myQoQ">
INSERT INTO Mytemptable (col1, col2, col3)
VALUES (#value1#, #value2#, #value3#)
</cfloop>
</cfquery>

SO... you'll only connect to the database once.. but the length of the SQL will be large... much much faster than looping over a cfquery tag

ASKER

The content table is about 2000 and growing (search results I'm less sure of at this time as I'm banging against development data). I'll move some more of the documents over and give it a go. Thanks so much, just wanted to make sure there wasn't a better way to do this.

ASKER

Thanks also zadoc, (I saw your response after I accepted _agx) :-)

ouch.. well I dont get points... but please use my solution... dont loop the cfquery tag.. loop within..

ASKER

Yep, will do. Thx again everyone

Yes, zadoc has a point as well. I would do two trials and compare the results. Keep in mind there are limitations on the single block of sql as well. So you may need to break it up into chunks of X queries.

(Use cfqueryparam for both)

1) Generating a separate query for each
<cfloop ...>
<cfquery...>
INSERT ...
</cfquery>
</cfloop>

2) Generating a large block of sql.

<cfquery...>
<cfloop ...>
INSERT
</cfloop>
</cfquery...>

> I would do two trials and compare the results

I pretty sure zadoc's suggestion will perform better - but I always encourage making informed decisions .. and seeing is believing ;-)

ASKER

Thanks so much ... you guys really go out of your way to help. :-)

I agree _agx_

I think its also something often overlooked by users that are not as familiar with SQL... SQL is extremely powerful and fast if used properly.