I think this works just as you wrote it. The problem is that the queries
that are being looped through are taking too long. This could be a hardware
issue or it could be an index problem. Those issues aside I would stop
trying to do this in Cold Fusion. I would consider either dumping the
entire dataset to a temporary table in SQL and pushing all of this
functionality into SQL in a stored procedure. Another option here, depending
on your setup, would be to write a DTS to do the work and kick off the
process with a SP you call from Cold Fusion.
I hope this is clear, if not get back with me.
John Hancock
-----Original Message-----
From: Ali Awan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 24, 2001 11:42 AM
To: CF-Talk
Subject: Transferring data between two datasources
I am trying to upload data from a tab delimited file into a SQL Server
database.
I have a separate datasource set up for the tab delimited file. The file is
about 50 megs roughly.
I'm querying the flat file and comparing it to the records in teh SQL Server
database and inserting the new ones.
My script keeps crunching but nothing seems to be uploaded and I dont' get
any timeout errors, database-wise or cold-fusion-wise.
I was wondering if anyone had had any experience with text file ODBC
connections, and also transferring large quantities of data between two
datasources.
Let me try and illustrate what I'm doing
<cfquery name="importinfo" datasource"textfiledatasource">
<!--- Select Data from Flat file -->
</cfquery>
<cfloop query="importinfo">
<cfquery datasource="sqlserverdatasource">
Insert column ID's into a temp table
</cfquery>
</cfloop>
<!-- I do a bunch of logic here where I set a flag in the temp table which
lets me know if the ID is a new one -->
The above stuff works
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------
This is where I run into trouble.....
<cfquery name="TempData" datasource="sqlserverdatasource">
select
ID,existingID
from
TempTable
</cfquery>
<cfloop query = "TempData">
<cfquery name="GetNewRecs" datasource="textfiledatasource>
SELECT *
FROM
Flat_File_Tablename
WHERE
ID = #TempData.ID#
</cfquery>
<cftry>
<!-- Existing ID is a flag I set in a table in my SQL Server
database that tells me whether the ID I have (which maps to one in the text
file) is for a record that is new (0) or existing(1), and I
only insert the new ones. Which I thought would take the load off of doing
a zillion inserts since there are tens of thousands of records in
the file.
-->
<cfif existingID EQ 0>
<cfquery datasource="sqlserverdatasource">
Insert with values from the GetNewRecs query
</cfquery>
</cfif>
<cfcatch type="Any">
Error Message
</cfcatch>
</cftry>
</cfloop>
There are no ODBC connection errors or database errors thrown, or any CF
errors that are thrown, or even server time-out errors that are thrown.
If anyone has any insight that would be appreciated.
Any help is greatly appreciated.
Names have been changed to protect the innocent.......
Ali
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
