DataTables vs. SQL Queries for Insertion

Hi All,

I have a situation where I'm working with three (sometimes four) datatables that require database select queries, and after some calculations and operations I need to insert my results into a new row. There are many similar processes - some are run daily, others weekly. The insertions themselves are numerous - daily ones are about 10,000 (6 columns) and the end-of-week ones can easily run to 40,000~50,000 (4 columns). These numbers are pretty reliable, and are unlikely to expand much in the future. Possibly 15,000 for the daily and 70,000 for end-of-week. What would be the best way to approach this? Should I be using DataTables and appending values to it, and then a bulk upload? or would it make more sense, if I'm looking for speed, to do individual Insert statements (creating a string each time). Would concurrency or multi-threading favour one option over the other, in case I want to experiment with further speed increases in the future? Which approach would you suggest for this problem?

Thanks in advance for your help.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Len La Sala17y ago
As I never use data tables or any other databound control I would use sql insert statements in between begin and end transaction so that if any problem is encountered a roll back is performed.

This could be done in a background thread or be concurrent.

Probable another advatage of using sql inserts is that you could save the individual statements to a file as a log of transactions which can then be executed within an SQL Server IDE if the need arises.