Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
 Posted - 2001-07-30 : 10:21:23
|
| Guys,I have been wrestling with this one for a while and have been barking up several wrong trees! You will probably recognise part of my problem from a variety of different previous posts. I think I now need some advice on the overall picture...I am supplied with a text file with some 250 fields and nearly 4 million records (Don't ask about the pros and cons of receiving data in this format!!! I have no control over it!). I need to load the data to SQL Server and carry out a fair amount of data processing upon loading - eg. calculate new variables (lots of case statements). I am trying to write a stored procedure that will load the data automatically, trapping errors in the input file, dealing with them appropriately and also reporting what has been done.I have come up with 3 approaches for this - each with their problems:-1) Load the data to a one field table (easy to automate and unlikely to fall over unless there is a problem with row delimiters or EOF markers in the raw file); then parse the contents of the field into the separate fields required. I have written a UDF to do this that works but is painfully slow (25 mins for less than 3000 records!) - probably due to 250+ fields!.2) Load the data to varchar fields by using xp_cmdshell to run a DTS package. This DTS package has dynamic properties so that the location of the raw file and such things as start and finish row can be passed into the "transfer data task". The problem with this is that if the layout changes you have to manually edit the DTS package so that the transformation properties (ie. mapping field on the source to field on the destination) are correct (The layout of the file is stored in an Excel spreadsheet that is loaded to SQL Server in another sp) *** I don't think I'll be able to alter this option to fully automate the process3) Load the data to varchar fields by using bulk insert/bcp. Problem with this is that I get an error specifying unexpected EOF marker sometimes - for a source datafile that I successfully loaded with DTS?!Does anyone have any bright ideas as to which way is the "best" way and where I should direct my effort??? Edited by - davidpardoe on 07/30/2001 10:21:59Edited by - davidpardoe on 07/30/2001 10:22:29 |
|
|
|
|
|
Loading Delimited ASCII Data