• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

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

Hello Experts.  I have a query of queries and I want to copy the entire result set into a temp table.  The only thing I can think of is to loop the result set and do an insert of each row.  There must be a better way???  Can anyone help?  As always, thanks for you time. K
0
whaleyk
Asked:
whaleyk
  • 6
  • 5
  • 4
  • +1
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
how about having a dts
0
 
Andrew MaurerCommented:
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
0
 
_agx_Commented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
erikTsomikSystem Architect, CF programmer Commented:
Hi agx, can you get back to my issue as well

0
 
_agx_Commented:
Hi Erik,

I just did. Let me know on the other thread if you have any questions.
0
 
whaleykAuthor Commented:
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.



0
 
_agx_Commented:
Since the QoQ is only in memory, you would have to copy into the #temp table row by row. Depending on the number of results it may or may not be worth it.

How big are your search results and how big is the the content table?  
0
 
Andrew MaurerCommented:
_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
0
 
whaleykAuthor Commented:
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.
0
 
whaleykAuthor Commented:
Thanks also zadoc, (I saw your response after I accepted _agx) :-)
0
 
Andrew MaurerCommented:
ouch.. well I dont get points... but please use my solution... dont loop the cfquery tag.. loop within..
0
 
whaleykAuthor Commented:
Yep, will do.  Thx again everyone
0
 
_agx_Commented:
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...>
0
 
_agx_Commented:
> 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  ;-)
0
 
whaleykAuthor Commented:
Thanks so much ... you guys really go out of your way to help.  :-)
0
 
Andrew MaurerCommented:
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.
0
 
_agx_Commented:
( @whaleyk - You are welcome.  Is it obvious we both enjoy what we do ? ;-)

> SQL is extremely powerful and fast if used properly.

Exactly.  The key is "used properly".  The performance difference between poorly designed and well designed sql often surprises people at first.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now