EasyCFM.COM ColdFusion Forums / General Discussion / inserting rows (from a loop result)

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: inserting rows (from a loop result)

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

gadjodilo
02-07-2006 @ 8:46 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 9
Joined: Oct 2005

Is it possible to insert loop results into a database table?

I have the following code:


<cfquery name="q" datasource="">
SELECT *
FROM inventimport WHERE PC_volgnr NOT IN (SELECT dev_volgnr FROM dev)
AND PC_volgnr <> 'geen pc aan' Order by inv_id
</cfquery>



<cfif q.recordcount GT 0><!--- anders niks zien --->
   <cfset temp = ValueList(q.PC_volgnr)>
   <CFLOOP INDEX="Teller" FROM=1 TO=#ListLen(temp)#>
       <cfset myString  = ListGetAt(temp,Teller)>
       <!--- of gebruik Mid()  --->
       <cfswitch expression="#Len(Trim(myString))#">
           <cfcase value="8"><!--- vb: BER-4999--->
               <cfset typenumber = Mid(myString,5,1)>
           </cfcase>
           <cfcase value="9"><!--- vb: B-06-4001--->
               <cfset typenumber = Mid(myString,6,1)>
           </cfcase>
           <cfcase value="11"><!--- vb: BER-06-4001--->
               <cfset typenumber = Mid(myString,8,1)>
           </cfcase>
           <cfdefaultcase>
           <cfset typenumber = "no typenr">
           </cfdefaultcase>
       </cfswitch>
       <cfset codenumber = LSParseNumber(Right(myString, 3))>
       <cfoutput>#myString# / #typenumber# / #codenumber#</cfoutput><br>
   </CFLOOP>
</cfif>


You see at the bottom the results. They are being showed in a loop:

<cfoutput>#myString# / #typenumber# / #codenumber#</cfoutput><br>

How can I insert these loop results into another database table? row by row?

Like this:

auto_id      column1          column2          column3
1       value1(myString)  value1(typenumber) value1(codenr
2       value2(mystring)  value2(typenumber) value2(codenr)

etc....

I tried something like this:

<CFQUERY NAME="update" DATASOURCE="rug">
   INSERT INTO dev
   (
   <CFLOOP query="q">
    
      dev_volgnr <CFIF #currentrow# NEQ #q.recordcount#>,</cfif>
    
   </cfloop>
   )
   VALUES
   (
    
     <CFLOOP query="q">
      '#myString#' <CFIF #currentrow# NEQ #q.recordcount#>,</cfif>
   </cfloop>
  
   )
</CFQUERY>        

But that doesn't work!!

Is there a solution? How would I do a thing like that? Is it even possible?

Hopefully you can help me a little with this.

Thank you in advance.

Gr, Kabbi

Webmaster
02-07-2006 @ 9:07 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

You can do this directly in the database....

<cfquery name="qTableCopy" datasource="MyDSN">
  INSERT INTO dev (dev_volgnr, PC_volgnr )
  SELECT inventimport.dev_volgnr, inventimport.PC_volgnr
  FROM inventimport
  WHERE PC_volgnr NOT IN (SELECT dev_volgnr FROM dev)
  AND PC_volgnr <> 'geen pc aan' Order by inv_id
</cfquery>


That is a quick example.. but basically the database server will simply insert into that tale from the other table... This is MUCH MUCH faster and will run better for you!

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email


Website Designed and Developed by Pablo Varando.