Check out: http://msdn.microsoft.com/en-us/library/ms188365.aspx
"For information about preparing data for bulk import, such as the requirements for importing data from a CSV data file, see..." On Tue, May 26, 2009 at 11:52 AM, Jason Durham <jdur...@cti-stl.com> wrote: > > I have been tasked with providing a mechanism to upload/parse/perform a > couple of checks/save CSV data. The current test file is 28,000 records > (~16 columns). I expect this file to grow in size each month, to an > indeterminate number (hundreds of thousands). > > > > It seems that I'll need to set a limit on the number of rows being > processed at each time. The two obvious options (in my eyes)... > > > > #1 break the CSV up into several smaller CSV files and write them back > to the file system before processing each individually (all checks > performed in memory) > > #2 set a large timeout value and attempt to load all of the records from > the CSV into a temp table, retrieving back smaller, more manageable > record sets > > > > Currently, I'm doing #2 but not limiting the number of records (28,000). > The data is getting into the temp table, being retrieved, but the final > insert is causing ColdFusion to hang. The final insert consists of one > cfquery with an internal loop over the "temp table" query object. The > final insert is within a single transaction so I can rollback if it > fails. > > > > This process will only take place on my development machine, connecting > to the live database. 2.6 Core 2 Duo, 4GB, Vista 32 with a 100MB > connection to production server running MS SQL 2005 Express. > > > > Perhaps I need to take a step back and consider alternatives. Btw, the > "checks" I'm performing are simply that 2 of the columns do not contain > a unique value (FKs). I'm currently doing this with QoQs but I could > probably push this off with a fairly complicated query to MS SQL. What > do the CF wizards suggest? > > > > TIA > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322779 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
