[RESOLVED] Inserting Large Amounts of Data

Hi all,

[.NET 3.5, Visual Studio C# 2008, OleDb connection, Access 2003]

As part of a database synchronisation program, I need to delete all the data in one table and replace it with the data in another. Currently I've been doing this by calling a DeleteAll() method, followed by looping over all the rows and calling a TableAdapter.Insert() method for each one.

This works, but it's slow: 5000 rows takes about half an hour, according to my users. For comparison, I can manually copy the same data in access in less than 5 seconds. That 5000 rows could easily end up as >50,000 rows by the end of the project, so that sort of performance is not an option.

I've been trying to use DataSetA.Table.Load(DataTableReader); and DataSetB.Table.CopyToDataTable(DataSetA.Table); but neither method has resulted in any change to the database. Either I don't understand how to use them properly, or they're not usable on designer-generated DataSets...

Is there any way of doing this? Any help would be appreciated.

Thanks,
Qu.

I don't think truncate is available in Access... so instead

1) Rename old table
2) Copy structure to new table (use previous name)
3) Drop old table
4) Fill-up new table with data

Populate a DataTable and call Update on your TableAdapter.

Jmcilhinney wins. Thanks man, I owe you many alcoholic beverages. I replaced my TableAdapter.Insert() with a Table.AddTableRow() and followed the process up with an Update, and it's running many times faster.

Quick follow-up question to assuage my curiosity: would there be any disadvantage to re-working all my foreach(Row) { Insert } loops to use AddTableRow? And if not, what use is TableAdapter.Insert(), aside from saving you an Update call?

Quote:

---

Originally Posted by Quasar6

Quick follow-up question to assuage my curiosity: would there be any disadvantage to re-working all my foreach(Row) { Insert } loops to use AddTableRow? And if not, what use is TableAdapter.Insert(), aside from saving you an Update call?

---

Using methods like TableAdapter.Insert is akin to using Command.ExecuteNonQuery. Such methods are designed for executing a single SQL statement. If you only want to insert one record then that would be an easier way than messing about with a DataTable. If you have multiple records to insert though, it's easier to populate the DataTable first and then save them as a batch.

Hah, that's brilliant! Not knowing that, I've been using Insert for batch saving. Re-working it will vastly improve performance on those functions. Thanks Jmc, I really do owe you. :D