When copying data to an SQL Server database, the SQLBulkCopy class gets the data there efficiently. When you have Access databases at both ends of the copy you don’t have a bulk copy option. The alternatives are passing the data through a DataSet, DataTable, DataReader or XML. Well, there is one more way that involves using the “IN” clause in an Access SQL statement. Let’s say that you need to copy the data from one table in one Access database to a table with the same structure in another Access database. The following kind of statement can accomplish that
INSERT INTO table SELECT * FROM table IN "c:\temp\source.mdb"
Just open up an
OleDbConnection to the destination database and execute the statement using
ExecuteNonQuery() and the database engine will open up the source database to get the records. The documentation for the IN clause suggests that quotes are not required but in practice they are. This should be more efficient than loading all of the data into memory, such as with a DataTable.