8 Replies Latest reply on Nov 5, 2009 3:28 PM by -==cfSearching==-

    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>

        • 1. Re: Select Into datasourse query from Cf created query
          Dan Bracuk Level 5

          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.

          • 2. Re: Select Into datasourse query from Cf created query
            ilssac Level 5

            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.

            • 3. Re: Select Into datasourse query from Cf created query
              -==cfSearching==- Level 4

              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.

              • 4. Re: Select Into datasourse query from Cf created query
                DLewey Level 1

                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.

                • 5. Re: Select Into datasourse query from Cf created query
                  ilssac Level 5

                  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.

                  • 6. Re: Select Into datasourse query from Cf created query
                    Dan Bracuk Level 5

                    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.

                    • 7. Re: Select Into datasourse query from Cf created query
                      DLewey Level 1

                      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.

                      • 8. Re: Select Into datasourse query from Cf created query
                        -==cfSearching==- Level 4

                        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.