Select Into datasourse query from Cf created query

[DLewey]

How do you select a query created with querynew() into  a datasource table?

Example code:

<cfset tempquery = querynew(columns)>
    <cfdump var="#tempquery#">
    <cfset row = 0>
    <cfloop list="#filecontent#" index="rowI" delimiters="#chr(10)#">
        <cfset count = 1>    
        <cfif findnocase("|",rowI)>
            <cfset row = row+1>
            <cfset addrow = queryaddrow(tempquery)>
            <cfloop list="#rowI#" delimiters="|" index="elementI">

                <cfset addtoquery = querysetcell(tempquery, "column#count#",elementI,row)>
                <cfset count = count + 1>
            </cfloop>
        </cfif>
    </cfloop>

    <!--- move the data into my temporary table --->
    <cfquery datasource="ds" name="function">
     Select *
     into destinationTable
     from #tempquery#
    </cfquery>

[Dan_Bracuk]

You have to do it record by record.  In your example, you might want to skip the querynew stuff and put an insert query inside that loop.

[ilssac]

I don't think you do, at least I have sure never heard of anything even close to this.

I would suspect the problem is that the recordset you built in our loop is an ColdFusion memeory space and the table you are trying to insert it into is in the databases memory space and they do not have an relationship between these two spaces and are not aware of each others relations.

The normal solution is to just do an insert query line by line while you are looping over the data.

Or to use some bulk loader feature of the database that can read structured file data directly into a similarly structured table.

But I don't think you are going to be able to blend a database solution and a ColdFusion solution like you have attempted here.

But if somebody does come up with a way to make this work, I would love to hear about it.

[DLewey]

I kept comming back to the fact that the two memory spaces were different. I was just hoping that there was a way that someone had figued out how to acommplish this without having to have a cfloop with an insert statement.

[ilssac]

Well, as I, and others, have mentioned; most database management systems have some mechanism to directly load text files.

ColdFusion would not necessarily be involved in such a solution, exccept maybe to put the text file somewhere handy for the database and possibly tell the database it is there and to start its file loading process.

[Dan_Bracuk]

You can put the loop inside the query instead of the query inside the loop.

insert into yourtable

(f1, f2)

select value1, value2

from some_small_table

where 1 = 2

<cfloop>

union

select #variable1#, #variable2#

from some_small_table

</cfloop>

With some dbs this is faster than individual queries.  With others, it's slower.  You can do your own testing.

[-__cfSearching__-]

How do you select a query created with querynew() into a

datasource table?

You cannot. Objects created with QueryNew() only exist in CF memory. Your database has no knowledge of them, nor can it communicate with an in memory query. To transfer the information, you need to loop through the query and add each record, to an existing table, using a sql INSERT statement.

Having said that, it looks like you are importing a text file. You may want to investigate what tools your database has for importing text files and use that instead. For example, MS SQL has BULK INSERT, MySQL has Load Data InFile, etcetera.

[DLewey]

The answer that you have all given just confirms my original thoughts that this was not possible via the method I was trying.  The hope was that i would be able use coldfusion to do more data validation before actually copying it to my database. I will have to go with the method of the SQL import feature because inserting over 16,000 rows individually take to long.

[-__cfSearching__-]

DLewey wrote:

The answer that you have all given just confirms my original thoughts that this was not possible via the method I was trying.  The hope was that i would be able use coldfusion to do more data validation before actually copying it to my database. I will have to go with the method of the SQL import feature because inserting over 16,000 rows individually take to long.

Yes, that is too much data to be handled with individual loops. For large imports, I use staging tables.  I first import the raw data into my staging table using my database's import tool.  Then the data in the staging tables is scrubbed with different SQL queries. Once it is scrubbed and validated, I  transfer the information to the main tables, via an INSERT/SELECT.