asked on

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.

PE_CF_DEV

<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

ASKER CERTIFIED SOLUTION

shooksm

membership

This solution is only available to members.

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

Start Free Trial

MoOTottle

ASKER

<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>

MoOTottle

ASKER

I got there in the end myself thanks lads.

but shooksm, your way looks much cleaner, is that suitable for large quantities of records?

shooksm

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.

SOLUTION

PE_CF_DEV

membership

This solution is only available to members.

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

Start Free Trial

PE_CF_DEV

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

shooksm

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.

MoOTottle

ASKER

@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.

PE_CF_DEV

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

MoOTottle

ASKER

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 :(