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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-01-12 : 07:56:06
|
| Alexis writes "I have to write this application wherein I need to upload a csv file (x # of rows with 20 attributes in each row) In my Vb app I am parsing each row in a loop and once all basic validation is done, I have to insert/update it depending upon a flag. There are 2 ways to do this that I know of, but both are killers on memory..#1 Insert for each row seperately in the main loop, so basically take one row...validate it and then insert//update it. Then move to next item.#2 Loop through all items and save all values in a seperate array and then keep calling Inserts/Updates in a loopI am looking for a better way of handling this and need all your expert advices....The avg # rows could be 500-800 and its ann http upload...Thanks for your help in advance.Alexis" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-12 : 07:58:28
|
| #3: Use bcp, DTS, or BULK INSERT to insert the entire file into a staging table, then perform the validation and insert/update the regular table.Whatever you do, stop using loops, they are performance killers. You can search the SQL Team forums for "staging table", you'll get a lot of hits but they'll give you lots of information too. Besides that we'll need more information on the file and table structures before we can give a more detailed response. |
 |
|
|
|
|
|
Best Way for Inserting/Updating couple of 100 records in SQL from Vb App