RE: [ cf-dev ] Importing large amounts of data to sql

[Albert Popkov]

The fastest way is bulk insert, and the key there to make sure that the operation will not go into transaction log (SQL books online has info about what needs to be done).   However, the problem with bulk insert is that it can't handle even minor problems in the data - in this case we normally do a simple pre-parser in either c++ or java which saves data into another text file and then run bulk insert.   DTS is the best when you need some flexibility for the transfer - e.g. multiply different data sources, some processing etc.   From: LIVE Support [mailto:[EMAIL PROTECTED] Sent: 10 June 2004 09:44 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Importing large amounts of data to sql Hi Steve,   Thanks for your input, its appreciated, although my main issue with using DTS is that the files are being uploaded by the client so the size of the file is unknown as is the filename. Also the data is required in the quickest time possible after the upload so I am after a solution which can be fast, dynamic and can replace the existing cfhttp process.         -----Original Message----- From: Steve Powell [mailto:[EMAIL PROTECTED] Sent: 09 June 2004 20:49 To: [EMAIL PROTECTED] Subject: Re: [ cf-dev ] Importing large amounts of data to sql   Would you not be better using a Data Transformations Service Package inside SQL Server?   We regularly import aorund 80Mb of data exported form our overnbight porcessing systems for live reports. It usually takes about 25 mins on a fairly lowly SQL box.   If all your CF is doing is reading the CSV and stuffing the data into columns in a SQL table a DTS will be vastly more efficient.   If the file is a standard format, you can set up the DTS using its wizard and save the packge. Then just replace the file each time and run the package again.   You can script inside DTS packages and get clever if you so wich, heirarchical data set and so on but that's less straightforward. there are some good books on DTS. Its a vastly mis-understood and under-estimated component inside SQL Server.       ----- Original Message ----- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 09, 2004 5:48 PM Subject: RE: [ cf-dev ] Importing large amounts of data to sql   Does this help? http://www.houseoffusion.com/cf_lists/index.cfm?method=messages&threadid=18243&forumid=4 Rafe