4 Replies Latest reply on Aug 16, 2007 1:35 PM by KomputerMan.com

    Append using a Structure

    KomputerMan.com Level 1
      I need to run an append query using a structure to pass in one of my data fields. This isn't a tough thing to do normally but this is a little abnormal. Basically I am migrating about 1.5 million records from a single table data dump into a relational database.

      The first query gets all unique BOX NUMBERS from the data dump and places them into a table called Box_Details. Then I go and get the Primary Key (PK) values for all of the unique boxes just added to the table. I store the PK and Box number in a structure called box_Struct.

      Now comes the hard part. I have to run another query from the same data dump and I need to replace the Box_Number field with the appropriate value contained in the box_Struct structure. The last query, called AddItem, shows what I am trying to do. Basically I need to pass the value from the SELECT query, which is ran inside the append (INSERT INTO) query, to the box_Struct structure so I can use the Box_ID as my foreign key in the item_description table. I know how to do it if I pull everything into CF but I have no desire to loop over 1.5 million records so I need a better way to do this substitution. Any ideas on how I can make this substitution inside the query work???


      <CFQUERY NAME="AddBoxDetail" DATASOURCE="#Request.DSN_RW#">
      INSERT INTO box_detail (trans_id, uval_trans_id, agency_id, box_type, box_number, box_desc, box_begin_date, box_end_date, stype_id, vault_loc, barcode, RotFreqCnt, Box_Date_In)
      SELECT DISTINCT #GetMax.MyMax# AS trans_id, 0 AS uval_trans_id, agency_id, box_type, box_number, box_desc, box_begin_date, box_end_date, stype_id, vault_loc, barcode, 1 AS RotFreqCnt, Box_Date_In
      FROM ImportRITSData
      </CFQUERY>

      <!--- Get the PKs of the records just added --->
      <CFQUERY NAME="GetBoxID" DATASOURCE="#Request.DSN_RW#">
      SELECT box_id, box_number
      FROM box_detail
      WHERE box_number IN (Select DISTINCT box_number FROM ImportRITSData)
      </CFQUERY>

      <CFSET box_Struct = StructNew()>
      <CFOUTPUT QUERY="GetBoxID"><CFSET box_Struct["#box_number#"] = box_id></CFOUTPUT>

      <CFQUERY NAME="AddItem" DATASOURCE="#Request.DSN_RW#">
      INSERT INTO item_description (item_type, item_number, box_id, form_number, years_inclusive, retent_reqmt, description)
      SELECT item_type, item_number, box_number, #box_Struct[box_number]# AS box_id, form_number, years_inclusive, retent_reqmt, description
      FROM ImportRITSData
      </CFQUERY>

      Thanks in advance for the help!!!
      Have an Ordianry Day...
      KomputerMan ~|:-)
        • 1. Re: Append using a Structure
          c_wigginton Level 1
          One issue is that in your final query, the input values are not matched, you have 7 fields but 8 values

          So, assuming that in your version the struct value is the extra value, and assuming that the box_number is a unique value you should be able to do this...

          INSERT
          INTO item_description
          (
          item_type ,
          item_number ,
          box_id ,
          form_number ,
          years_inclusive,
          retent_reqmt ,
          description
          )
          SELECT item_type ,
          item_number ,
          (select b.box_id from box_detail b where ird.box_number = b.box_number) AS box_id,
          form_number ,
          years_inclusive ,
          retent_reqmt ,
          description
          FROM ImportRITSData ird

          • 2. Re: Append using a Structure
            Dan Bracuk Level 5
            If you join the tables, you should be able to do it all in one query.
            • 3. Re: Append using a Structure
              Level 7
              > This isn't a tough thing to do normally but this is a little abnormal.
              > Basically I am migrating about 1.5 million records from a single table data
              > dump into a relational database.

              Just to cut you off @ the pass a bit... CF is not the right tool for the
              job here. Try to get all this done on the DB server itself. None of your
              CF code suggests there's anything going on that couldn't be better handled
              but the DB.

              CF excels @ doing quick DB queries to build web pages, but it's pretty
              shite at coping with large record sets. You could monkey around splitting
              the process across multiple requests and forcing GC and what not, but... if
              you're manipulating data, use the right tool for the job: the database.

              --
              Adam
              • 4. Re: Append using a Structure
                KomputerMan.com Level 1
                Thanks for all the help out there!!! I did forget to take out the Box_number from my third query before I posted it... that was just part of me playing around. But the nested sub query did what I needed it to do perfectly. Thanks again!!!

                Have an Ordinary Day...
                KomputerMan ~|:-)