>> So trying to access the file via SQL Server 2000 probably isn't possible.
Unfortunately that probably cuts out your best route, I import a fairly substantial amount of log data into my application from CSV files and I used a query/stored proc and the BULK INSERT function from SQL, it's very good with this kind of thing, you would move it into a staging table and then use another query to do your update/replace type statement, it would be super efficient. However, it all depends on how often you import the data, SQL server does have some other tricks up its sleeve, such as DTS which can be used for large file imports, but they'll required more substantial access to the server, whereas BULK INSERT can be run from a normal query, if you want more information and an example of the code, let me know. Thanks, Rob -----Original Message----- From: Roxie R [mailto:[EMAIL PROTECTED] Sent: 07 June 2007 13:11 To: CF-Talk Subject: Reading/Importing Large Text Files into a Database I have been having this ongoing battle with large files (3 MB or more). Basically each file is a comma delimited text file that on every line has information that needs to be checked against the database and then either updated or inserted. Right now I loop through every line in the file and using CFQUERY to do the database check and insert/update. But with the files being 3 MB, it is slow and timesout. The customer is not happy. For the database check I use "IF EXISTS (....) UPDATE .... ELSE INSERT ...." The ISP is using CF Server MX 6 and SQL Server 2000. I cannot use CFFILE for security reasons, they created their own CFX tag to accomodate uploads. So trying to access the file via SQL Server 2000 probably isn't possible. Is there a better way to deal with large files and trying to get them into the database? Any ideas would be appreciated! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:280359 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
