3 Replies Latest reply on Oct 1, 2007 2:18 PM by kjs76

    Splitting a list (loop and insert)

    Level 7
      I have a text file with about 65000 records to be looped and inserted into a
      database. I am using cfhttp to read the list and turn it into a query. I am
      then using that query to loop and insert the records into a database.
      However, its too large and the server is timing out. Is there a break the
      list into 2 pieces and do it in serial?

      I am using:

      <cffunction name="getResidential" access="private" returntype="void"
      output="false" hint="">
      <cfargument name="ResFile" type="string" required="yes">
      <cfhttp timeout="6600"
      url=" http://www.mywebsite.com/assets/property/#ResFile#" method="GET"
      name="Property" delimiter="|" textqualifier="" firstrowasheaders="yes" />
      <cfloop query="Property">
      <cfquery name="loopProperty" datasource="bpopros">

      INSERT STATEMENT HERE

      </cfquery>
      </cfloop>
      </cffunction>

      Is it possible to do something like:

      Function 1
      <cfloop from="1" to="40000" index="i">

      </cfloop>

      Function 2
      <cfloop from="40001" to="#Query.RecordCount#" index="i">

      </cfloop>

      Any ideas? Thanks

      --
      Wally Kolcz
      MyNextPet.org
      Founder / Developer
      586.871.4126


        • 1. Re: Splitting a list (loop and insert)
          Dan Bracuk Level 5
          It's possible, but it's a better idea to look for ways to do it without cold fusion.

          Food for thought, I have a requirement to move data from one db to another. I use Cold Fusion to query the first db, output to text, and ftp the text files to another server. I also have a scheduled job that looks for these text files and loads them into db2.
          • 2. Re: Splitting a list (loop and insert)
            Level 7
            I like your second solution, but wouldn't I need access to the actual web
            server? I need to do this on hosting. I am downloading the files from a Real
            Comp and then looping the data into my client's database for searching. I
            wanted to just use the files as a flat database and just query or query off
            of it, but there is a new file everyday. Only one update (Sunday) is the
            master list of 45-60k records. All the rest (Mon-Sat) are just small
            updates.

            "Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
            news:fdrgde$6qu$1@forums.macromedia.com...
            > It's possible, but it's a better idea to look for ways to do it without
            > cold
            > fusion.
            >
            > Food for thought, I have a requirement to move data from one db to
            > another. I
            > use Cold Fusion to query the first db, output to text, and ftp the text
            > files
            > to another server. I also have a scheduled job that looks for these text
            > files
            > and loads them into db2.
            >


            • 3. Re: Splitting a list (loop and insert)
              kjs76
              There is a timeout on the cfhttp, but what about the page running your function?

              Might try a <cfsetting requesttimeout="6600"> on the page running the function as well.