What are other options?
I've done similar things in the past, often to copy over data from a database
structure that wasn't normalized
into a database structure that is.
The most common reason for a denormalized table structure (in my experience, of
course) is bad development. I
have been in situations where the users needs and use of an application has changed
since it's original
development. With those changes the table structure needs to be changed. I've used
code similar in concept to
copy over data.
Enough rambling, what are other options? For a one-time deal, I'd write the code
in CF and let it run for the
12 hours. If you want to do this on a regular basis, exploring other options is a
good idea.
Daniel Lancelot wrote:
> don't use CF...
>
> -----Original Message-----
> From: Art Broussard [mailto:[EMAIL PROTECTED]]
> Sent: 13 February 2001 15:57
> To: CF-Talk
> Subject: What else can I say but HELP!!!!
>
> I am doing a query to get all the records, 43224, from a dbase4 database
> that was created from a tab delemeted file.
> I then check each record to see if it is in an Oracle table with 52,000
> records and still growing.
> If it is then update that record.
> If not, insert that record.
>
> So far it takes about 10 min just to get through the first 500
> inserts/updates.
> Using my bad math skills, it should take over 12 hours to do this!
>
> Any one have any ideas on how to reduce the time it takes to do all of this?
>
> Phillip
>
> Here is my code.
>
> <cfquery name="get" datasource="POUTDOORS_DBF">
> select * from poutdoors_dbf
> </cfquery>
>
> <cfoutput>Total records from dbase4: <b>#get.RecordCount#</b></cfoutput><P>
> <ol start="1">
> <cfoutput>
> <cfloop query="get" startrow="1" endrow="#get.RecordCount#">
> <cfquery name="check" datasource="#application.datasource#">
> select text_id, proddate from prodheader
> where itemno2 = '#itemno2#' and
> itemno3 = '#itemno3#' and
> itemno4 = '#itemno4#'
> </cfquery>
>
> <cfif check.RecordCount is "0">
>
> <cfquery name="put" datasource="#application.datasource#">
> insert into prodheader
> values(prodheaderID.NextVal,
> 'Off',
> '#DateFormat(Now(), "dd-mmm-yyyy")#',
> 0,
> '#itemno2#',
> '#itemno3#',
> '#itemno4#',
> #dept#,
> #subdept#,
> #class#,
> #subclass#,
> #text_id#,
> '#RTrim(header)#',
> '#RTrim(status)#')
> </cfquery>
>
> </cfif>
> <cfif check.RecordCount gt "0" and check.proddate is not
> "#DateFormat(Now(), "dd-mmm-yyyy")#">
>
> <cfquery name="add" datasource="#application.datasource#">
> update prodheader
> set proddate = '#DateFormat(Now(), "dd-mmm-yyyy")#',
> text_id = #text_id#,
> header = '#RTrim(header)#',
> bpstatus = '#RTrim(status)#'
> where itemno2 = '#itemno2#' and
> itemno3 = '#itemno3#' and
> itemno4 = '#itemno4#'
> </cfquery>
>
> </cfif>
> <li>RECORD<br>
> </cfloop>
> </cfoutput>
> </ol>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
