Hi

I have got a question around the application we are in the process of designing. The VB6 app runs with SQL server and need to do a few million record inserts upon data input and a few hundred thousand updates after certian calculations. I would like to know of what the best way of doing this is?

I thought of using a SQL SP to do all the inserts, selects and Updates. What is the advantage in doing this? Would it remove some load on the VB6 app on the users PC? How would I handle errors? If I insert and commit after each record and record 1Million returns error...how would I rollback or allow for error to be fixed and continue? Would it be possible for me to build an Array of SQL queries and pass the array to the SQL SP and do all the processing and only commit right at the end?

I am toying around with tons of ideas at the moment and any input or sugestions would be highly appreciated.

Regards
Marnus