[cfaussie] Re: Inserting Queries
If your tables have some type of related index you can also use the undocumented JAVA indexOf() method of the query object. Especially if you will need to run these query's often. Ben Nadel has a case study. http://www.bennadel.com/blog/267-Ask-Ben-ColdFusion-Optimization-A-Case-Study.htm Or better yet use Ben's technique of creating Structures from querys for super fast index lookups. http://www.bennadel.com/blog/268-Structs-As-Query-Indexes-Speed-And-Rick-Osborne.htm <http://www.bennadel.com/blog/268-Structs-As-Query-Indexes-Speed-And-Rick-Osborne.htm>Very handy for combining query's from separate data sources. On Tue, Jul 21, 2009 at 8:43 PM, Simon Haddon wrote: > That's about right. > > The insert statement is being run on the server in the RDBMS and it knows > nothing about your memory query. I am guessing you were getting an SQL error > back about a missing table? > > Kevin has it right :) > > > > 2009/7/22 KNOTT, Brian > > >> Thanks Barry. Could just do a loop, as hoping to do one big insert but >> you don't seem to be able get data form one datasource and insert into >> another, in the same cfquery. >> >> Brian Knott >> ext 31221 >> >> >> -Original Message- >> From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On >> Behalf Of Barry Beattie >> Sent: Wednesday, 22 July 2009 10:35 AM >> To: cfaussie@googlegroups.com >> Subject: [cfaussie] Re: Inserting Queries >> >> >> Hi Brian >> >> queryInMomory (sic: "momery"?) has multiple rows, yes? >> >> if so, anything wrong with a loop over the in-memory query doing >> individual db inserts for each loop iteration? >> >> the thing to keep in mind is that the in-memory query is nothing more >> than a CF memory data structure, not a db query, view or table. >> basically a struct of arrays. >> >> in other words, two different "layers", you need to take the data out >> of the "CF layer" (in-memory query) and put it into the "db layer" >> (inserts into db tables) >> >> just a thought >> barry.b >> >> >> >> >> >> >> >> >> On Wed, Jul 22, 2009 at 10:21 AM, KNOTT, >> Brian wrote: >> > Guys having a slow brain day. >> > >> > I have a query that I have created from code using the QueryNew command. >> > >> > I now want to insert this into a database, something like >> > >> > >> > INSERT INTO databaseTable >> > SELECT Title, title as Modelname, ModelPath, ModelEmailString, >> > CubePublishPath, PPESPublishPath, ModelUserSecurity, >> > ModelBuildExtraCommandsPre, ModelBuildExtraCommandsPost, BuildLevel, >> > BusinessName, Duration, Keywords, NotifyStatus, MajorGrouping, >> > MinorGrouping, ModelLabel, Frequency, Status, PrimaryMDC, ItemType, Path >> > FROM queryInMomory >> > >> > >> > Thanks >> > >> > Brian Knott >> > Software Engineer (COGNOS Developer) | MIS Front End | Banking & Life >> MIS >> > ext 31221 ph 07 1221 >> > >> > >> > >> > This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or one >> of >> > its related entities "Suncorp". >> > Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane or on >> 13 >> > 11 55 or at suncorp.com.au. >> > The content of this e-mail is the view of the sender or stated author >> and >> > does not necessarily reflect the view of Suncorp. The content, including >> > attachments, is a confidential communication between Suncorp and the >> > intended recipient. If you are not the intended recipient, any use, >> > interference with, disclosure or copying of this e-mail, including >> > attachments, is unauthorised and expressly prohibited. If you have >> received >> > this e-mail in error please contact the sender immediately and delete >> the >> > e-mail and any attachments from your system. >> > If this e-mail constitutes a commercial message of a type that you no >> longer >> > wish to receive please reply to this e-mail by typing Unsubscribe in the >> > subject line. >> > >> > > >> > >> >> >> >> >> > > > -- > Cheers > Simon Haddon > > Woman loves feeling danger and speed. That is why woman wants man. They > get a speed rush that is the most dangerous of all. > > > > > -- -- if you've written a homegrown[*CFML] controller layer that rivals the sophistication and ease of use of the three major players (MG/MII/FB) - then share it or shut up. You code in a silo - that's great. Just stay in there and don't try to tell everyone how good it smells. --Dave Ross --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Inserting Queries
That's about right. The insert statement is being run on the server in the RDBMS and it knows nothing about your memory query. I am guessing you were getting an SQL error back about a missing table? Kevin has it right :) 2009/7/22 KNOTT, Brian > > Thanks Barry. Could just do a loop, as hoping to do one big insert but you > don't seem to be able get data form one datasource and insert into another, > in the same cfquery. > > Brian Knott > ext 31221 > > > -Original Message- > From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On > Behalf Of Barry Beattie > Sent: Wednesday, 22 July 2009 10:35 AM > To: cfaussie@googlegroups.com > Subject: [cfaussie] Re: Inserting Queries > > > Hi Brian > > queryInMomory (sic: "momery"?) has multiple rows, yes? > > if so, anything wrong with a loop over the in-memory query doing > individual db inserts for each loop iteration? > > the thing to keep in mind is that the in-memory query is nothing more > than a CF memory data structure, not a db query, view or table. > basically a struct of arrays. > > in other words, two different "layers", you need to take the data out > of the "CF layer" (in-memory query) and put it into the "db layer" > (inserts into db tables) > > just a thought > barry.b > > > > > > > > > On Wed, Jul 22, 2009 at 10:21 AM, KNOTT, > Brian wrote: > > Guys having a slow brain day. > > > > I have a query that I have created from code using the QueryNew command. > > > > I now want to insert this into a database, something like > > > > > > INSERT INTO databaseTable > > SELECT Title, title as Modelname, ModelPath, ModelEmailString, > > CubePublishPath, PPESPublishPath, ModelUserSecurity, > > ModelBuildExtraCommandsPre, ModelBuildExtraCommandsPost, BuildLevel, > > BusinessName, Duration, Keywords, NotifyStatus, MajorGrouping, > > MinorGrouping, ModelLabel, Frequency, Status, PrimaryMDC, ItemType, Path > > FROM queryInMomory > > > > > > Thanks > > > > Brian Knott > > Software Engineer (COGNOS Developer) | MIS Front End | Banking & Life MIS > > ext 31221 ph 07 1221 > > > > > > > > This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or one > of > > its related entities "Suncorp". > > Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane or on > 13 > > 11 55 or at suncorp.com.au. > > The content of this e-mail is the view of the sender or stated author and > > does not necessarily reflect the view of Suncorp. The content, including > > attachments, is a confidential communication between Suncorp and the > > intended recipient. If you are not the intended recipient, any use, > > interference with, disclosure or copying of this e-mail, including > > attachments, is unauthorised and expressly prohibited. If you have > received > > this e-mail in error please contact the sender immediately and delete the > > e-mail and any attachments from your system. > > If this e-mail constitutes a commercial message of a type that you no > longer > > wish to receive please reply to this e-mail by typing Unsubscribe in the > > subject line. > > > > > > > > > > > > > -- Cheers Simon Haddon Woman loves feeling danger and speed. That is why woman wants man. They get a speed rush that is the most dangerous of all. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Inserting Queries
Thanks Barry. Could just do a loop, as hoping to do one big insert but you don't seem to be able get data form one datasource and insert into another, in the same cfquery. Brian Knott ext 31221 -Original Message- From: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] On Behalf Of Barry Beattie Sent: Wednesday, 22 July 2009 10:35 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Inserting Queries Hi Brian queryInMomory (sic: "momery"?) has multiple rows, yes? if so, anything wrong with a loop over the in-memory query doing individual db inserts for each loop iteration? the thing to keep in mind is that the in-memory query is nothing more than a CF memory data structure, not a db query, view or table. basically a struct of arrays. in other words, two different "layers", you need to take the data out of the "CF layer" (in-memory query) and put it into the "db layer" (inserts into db tables) just a thought barry.b On Wed, Jul 22, 2009 at 10:21 AM, KNOTT, Brian wrote: > Guys having a slow brain day. > > I have a query that I have created from code using the QueryNew command. > > I now want to insert this into a database, something like > > > INSERT INTO databaseTable > SELECT Title, title as Modelname, ModelPath, ModelEmailString, > CubePublishPath, PPESPublishPath, ModelUserSecurity, > ModelBuildExtraCommandsPre, ModelBuildExtraCommandsPost, BuildLevel, > BusinessName, Duration, Keywords, NotifyStatus, MajorGrouping, > MinorGrouping, ModelLabel, Frequency, Status, PrimaryMDC, ItemType, Path > FROM queryInMomory > > > Thanks > > Brian Knott > Software Engineer (COGNOS Developer) | MIS Front End | Banking & Life MIS > ext 31221 ph 07 1221 > > > > This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or one of > its related entities "Suncorp". > Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane or on 13 > 11 55 or at suncorp.com.au. > The content of this e-mail is the view of the sender or stated author and > does not necessarily reflect the view of Suncorp. The content, including > attachments, is a confidential communication between Suncorp and the > intended recipient. If you are not the intended recipient, any use, > interference with, disclosure or copying of this e-mail, including > attachments, is unauthorised and expressly prohibited. If you have received > this e-mail in error please contact the sender immediately and delete the > e-mail and any attachments from your system. > If this e-mail constitutes a commercial message of a type that you no longer > wish to receive please reply to this e-mail by typing Unsubscribe in the > subject line. > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Inserting Queries
The easiest way would be to loop over the initial query and either make each DB insert a new inline query, or you can make a that does the insert and call the method from the loop. eg INSERT INTO .VALUES ( "#arguments.value1#", "#arguments.value1#") AD HOK CODE!! On Tue, Jul 21, 2009 at 8:21 PM, KNOTT, Brian wrote: > Guys having a slow brain day. > > I have a query that I have created from code using the QueryNew command. > > I now want to insert this into a database, something like > > > INSERT INTO databaseTable > SELECT Title, title as Modelname, ModelPath, ModelEmailString, > CubePublishPath, PPESPublishPath, ModelUserSecurity, > ModelBuildExtraCommandsPre, ModelBuildExtraCommandsPost, BuildLevel, > BusinessName, Duration, Keywords, NotifyStatus, MajorGrouping, > MinorGrouping, ModelLabel, Frequency, Status, PrimaryMDC, ItemType, Path > FROM queryInMomory > > > Thanks > > *Brian Knott* > Software Engineer (COGNOS Developer) | MIS Front End | Banking & Life MIS > *ext* 31221* ph* 07 1221 > > > > * * > This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or one of > its related entities "Suncorp". > Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane or on 13 > 11 55 or at suncorp.com.au. > The content of this e-mail is the view of the sender or stated author and > does not necessarily reflect the view of Suncorp. The content, including > attachments, is a confidential communication between Suncorp and the > intended recipient. If you are not the intended recipient, any use, > interference with, disclosure or copying of this e-mail, including > attachments, is unauthorised and expressly prohibited. If you have received > this e-mail in error please contact the sender immediately and delete the > e-mail and any attachments from your system. > If this e-mail constitutes a commercial message of a type that you no > longer wish to receive please reply to this e-mail by typing Unsubscribe in > the subject line. > > > > -- -- if you've written a homegrown[*CFML] controller layer that rivals the sophistication and ease of use of the three major players (MG/MII/FB) - then share it or shut up. You code in a silo - that's great. Just stay in there and don't try to tell everyone how good it smells. --Dave Ross --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Inserting Queries
Hi Brian queryInMomory (sic: "momery"?) has multiple rows, yes? if so, anything wrong with a loop over the in-memory query doing individual db inserts for each loop iteration? the thing to keep in mind is that the in-memory query is nothing more than a CF memory data structure, not a db query, view or table. basically a struct of arrays. in other words, two different "layers", you need to take the data out of the "CF layer" (in-memory query) and put it into the "db layer" (inserts into db tables) just a thought barry.b On Wed, Jul 22, 2009 at 10:21 AM, KNOTT, Brian wrote: > Guys having a slow brain day. > > I have a query that I have created from code using the QueryNew command. > > I now want to insert this into a database, something like > > > INSERT INTO databaseTable > SELECT Title, title as Modelname, ModelPath, ModelEmailString, > CubePublishPath, PPESPublishPath, ModelUserSecurity, > ModelBuildExtraCommandsPre, ModelBuildExtraCommandsPost, BuildLevel, > BusinessName, Duration, Keywords, NotifyStatus, MajorGrouping, > MinorGrouping, ModelLabel, Frequency, Status, PrimaryMDC, ItemType, Path > FROM queryInMomory > > > Thanks > > Brian Knott > Software Engineer (COGNOS Developer) | MIS Front End | Banking & Life MIS > ext 31221 ph 07 1221 > > > > This e-mail is sent by Suncorp-Metway Limited ABN 66 010 831 722 or one of > its related entities "Suncorp". > Suncorp may be contacted at Level 18, 36 Wickham Terrace, Brisbane or on 13 > 11 55 or at suncorp.com.au. > The content of this e-mail is the view of the sender or stated author and > does not necessarily reflect the view of Suncorp. The content, including > attachments, is a confidential communication between Suncorp and the > intended recipient. If you are not the intended recipient, any use, > interference with, disclosure or copying of this e-mail, including > attachments, is unauthorised and expressly prohibited. If you have received > this e-mail in error please contact the sender immediately and delete the > e-mail and any attachments from your system. > If this e-mail constitutes a commercial message of a type that you no longer > wish to receive please reply to this e-mail by typing Unsubscribe in the > subject line. > > > > --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
