Sorry to hear that you cannot stop the querying process. This will limit your options.

Insert cursors are a way for your Esql/C process to buffer the inserts before they are committed to the database. I cannont comment on how that will affect the performance of your system overall, you would have to measure that. But they are the fastest way for you to insert records into a table via your program. After your load has been completed I recommend you still run update statistics.

As far a performance goes, you may want to pre-process the file and peform your validations before you attempt an insert run. Any exception records can be excluded from the insert run.

Since you have to run a large load in the exisiting environment and it is a one off, my best suggestion would be to validate and then load the file in much smaller chunks. Say 10,000 records at a time.

Therefore split the file into multiple files of 10,000 record chunks.
Write a validation program to validate a single file.
Write a load program to load a single file.

If you dont want to manage this all manually then,

write another(!) program to manage the loading of the small 10K files. You could create a table to store the name of the file, total number of rows, number of rows validated, rows loaded. This program would limit the number of concurrent loading processes (e.g. 10 at a time) and continue until all files have completed. You could add extra features such a reloading a failed file or allowing processing during a specific time frame.

Overall the loading process may take some time to complete, but I think you are now in a situation where you should be trying to spread and manage the workload.

Anyway, given all that you have told me that is how I would approach this task and I do appreciate that what I have described is not a trivial piece of work. (Loading high volumes of data to a live database never is )

MBB