[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-24
17
Medium Priority
?
444 Views
Last Modified: 2013-12-24
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
Comment
Question by:whaleyk
  • 6
  • 5
  • 4
  • +1
17 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 23725897
how about having a dts
0
 
LVL 9

Expert Comment

by:Andrew Maurer
ID: 23725933
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
 
LVL 52

Expert Comment

by:_agx_
ID: 23726075
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
LVL 19

Expert Comment

by:erikTsomik
ID: 23726098
Hi agx, can you get back to my issue as well

0
 
LVL 52

Expert Comment

by:_agx_
ID: 23726181
Hi Erik,

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

Author Comment

by:whaleyk
ID: 23726186
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 23726274
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
 
LVL 9

Expert Comment

by:Andrew Maurer
ID: 23726327
_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
 

Author Closing Comment

by:whaleyk
ID: 31550754
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
 

Author Comment

by:whaleyk
ID: 23726351
Thanks also zadoc, (I saw your response after I accepted _agx) :-)
0
 
LVL 9

Expert Comment

by:Andrew Maurer
ID: 23726352
ouch.. well I dont get points... but please use my solution... dont loop the cfquery tag.. loop within..
0
 

Author Comment

by:whaleyk
ID: 23726371
Yep, will do.  Thx again everyone
0
 
LVL 52

Expert Comment

by:_agx_
ID: 23726407
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
 
LVL 52

Expert Comment

by:_agx_
ID: 23726436
> 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
 

Author Comment

by:whaleyk
ID: 23726458
Thanks so much ... you guys really go out of your way to help.  :-)
0
 
LVL 9

Expert Comment

by:Andrew Maurer
ID: 23726466
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
 
LVL 52

Expert Comment

by:_agx_
ID: 23726690
( @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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses
Course of the Month19 days, 7 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question