Execute multiple dynamic SQL from CF

Hi lads, I have a script that works in ASP, and want to convert it to CF (due to certain issues)

basically the ASP script does the follows: -

1)Selects a number of strings (which contain SQL SELECT statements) from a database.

2)Creates a temporary Table.

3)Uses the recordset of SQL selected in step 1. to select data into the temp table.



MoOTottleAsked:
Who is Participating?
 
shooksmConnect With a Mentor Commented:
I would agree with using a stored procedure but here is how you might do it if a stored procedure is not an option.

<cfquery datasource="#strDatasource#" name="qrySelectStatements">
      SELECT SelectField
            FROM TableWithSelectStatements
</cfquery>
<cfquery datasource="#strDatasource#" name="qryTempTable">
      SELECT *
            FROM TempTable
</cfquery>
<cfset intUniqueQueryID = 1>
<cfloop query="qrySelectStatements">
      <cfquery dbtype="query" name="qryTemp#intUniqueQueryID#">
            #qrySelectStatements.SelectField#
                  FROM qryTempTable
      </cfquery>
      <cfset intUniqueQueryID = IncrementValue(intUniqueQueryID)>
</cfloop>
0
 
PE_CF_DEVCommented:
<cfquery name="name" datasource="your datasource">
SET NOCOUNT OFF
your SQL you are using
SET NOCOUNT ON
</cfquery>

Is one option  however by the sounds of it you would be better off creating a stored procedure in SQL and then executing it with coldfusion using <cfstoredproc>.

<cfquery> Info:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b19.htm

<cfstoredproc> info:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-pc9.htm#wp2607555
0
 
MoOTottleAuthor Commented:
<cfquery name="get_usergroups" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">
        #strSQL#
</cfquery>

<cfset iCounter = 0>

<cfSET tmpTableName="tmp_"&skeywordFilter&dateformat(now(),'yyyymmdd')&TimeFormat(now(), "hhmmss")>

<cfset tmpTableCounter=1>
<cfset tmpQueryName="sub_usergroups"&tmpTableCounter>
<cfquery name="create_temptable" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">

            someSQL
</cfquery>

<cfoutput>
      
<cfloop query="get_usergroups">

      <cfquery name="#Evaluate("tmpQueryName")#" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">
            #group_sql#
      </cfquery>


      <cfloop query="#Evaluate("tmpQueryName")#">

            <cfquery datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">

            INSERT INTO #tmpTableName# (fields)
            VALUES;

            </cfquery>
      </cfloop>
                        
                        

                        
<cfset tmpTableCounter=tmpTableCounter+1>
<cfset tmpQueryName="sub_usergroups"&tmpTableCounter>
</cfloop>
</cfoutput>
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
MoOTottleAuthor Commented:
I got there in the end myself thanks lads.

but shooksm, your way looks much cleaner, is that suitable for large quantities of records?
0
 
shooksmCommented:
Not sure.  That was just something I threw together quickly.  For moving data around, I much prefer more powerful tools likes DTS or BCP and leave Cold Fusion to just displaying result sets and not data manipulation.
0
 
PE_CF_DEVConnect With a Mentor Commented:
Thats a lot of connections to the database...That gets to be a lot of overhead

you might try something like doing a join with the get_usergroups  and the following query:
     <cfquery name="#Evaluate("tmpQueryName")#" datasource="#dsn#" dbtype="ODBC" username="#DSN_UN#" password="#DSN_PW#">
          #group_sql#
     </cfquery>

Then using <cfoutput group="columnname"> you can group them together for each user.
so it would be
<cfoutput group="columnname">
 anything you want to do for each user
<cfoutput>
anything you want to do for stuff that pertains to te temquery
</cfoutput>
</cfoutput>

Connecting to a databse is expensive in terms of system resources and it makes you more vulnerable to network problems. Try to limit it as much as possible :)
0
 
PE_CF_DEVCommented:
If all you want to do is move stuff from one table to another try:

INSERT INTO TABLE (Columns)
Select COLUMNS
from table_to_move_stuff_from
Where and limiters
0
 
shooksmCommented:
I aggree with PE_CF_DEV as the solution with the INSERT INTO with SELECT will hands down outperform a cold fusion solution using looping.
0
 
MoOTottleAuthor Commented:
@PE_CF_DEV, so loop through each of my tempory SELECT statments but instead of using my current method force a "INSERT INTO TABLE (Columns)" before the custom SQL?

Offtopic I am real busy this week guys, so wont get chance to allocate points til the end of the week when i can test the code/suggestions out.
0
 
PE_CF_DEVCommented:
Well I am not sure if it will work in this senerio but. What I was saying is if the end result is taking information from one or many tables and insterting it into a new or different table the best way of doing that is like this (things in all caps are thing you will want to replace):

Insert into ENDTABLE (COLUMN1, COLUMN2, COLUMN3)
/*now here instead of inserting 1 record like this Values (value1,value2,value3) we use a select statement*/
Select INFO1, INFO2, INFO3
from BASETABLE1 IINER JOIN BASETABLE1 ON BASETABLE1.PRIMARYKEY = BASETABLE2.FORGIENKEY
WHERE ANYTHING YOU NEED TO LIMIT

HTH
0
 
MoOTottleAuthor Commented:
oh i see what your saying PE_CF_DEV, but the nature of my records & tabel structure means the SQL is impossible to draft in this instance.

I'll split the points between you both though.

Sorry its been such a long time, i must admit i forgot about this question :(
0
All Courses

From novice to tech pro — start learning today.