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
