Bulk Copy between Microsoft Access Databases using ADO.NET

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.

Advertisements
Tagged with:
Posted in Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: