CODEWITHSUNDEEP
sqlvb.netado.netoledb
Logan
Logan

Reputation: 329

Copying a table from one database into a database on a different connection (ADO.NET)

I am trying to copy a table in a database into another database on another connection in VB.NET, using OleDb. If they were on the same connection I would just use SELECT INTO, but they are not. I have two different OleDbConnection and cannot see an easy way to do this.

Right now I am attempting to just copy the database into a DataTable using an OleDbDataAdapter, and then loop through the DataTable and insert every record into the target database one at a time. This obviously takes a ton of time for the large DB I could potentially be dealing with, and I have to deal with escaping strings, null values, etc.

Is there an easier way to do this?

Thanks, Logan

edit - just to make this more clear: I have two OleDbConnection objects, one is linked directly to a local .mdb file on my computer (JET). The other is linked to a database on our servers (SQLOLEDB). I am wanting to do this:

"SELECT * FROM fromDB INTO toDB"

But I can't because fromDB and toDB are on different connections, and the OleDbCommand object is only attached to one. The only way I can see how to do this is to connect to fromDB, copy it into a DataTable, connect to toDB, and copy all of the data in the DataTable row by row into toDB. I was wondering if there is an easier way to do this.

Upvotes: 1

Views: 3595

Answers (2)

PeskyGnat
PeskyGnat

Reputation: 2464

If you are constrained to this architecture, one idea is to write a stored procedure on the server that accepts a large chuck of row data in one call. It could then write out the row data to a file for a future bulk-insert, or it could attempt to insert the rows directly.

This also has the benefit of speeding things up over high latency connections to the server.

Also, if you use parameterized statements, you can avoid having to escape strings etc.

Upvotes: 0

BonyT
BonyT

Reputation: 10940

If you are just copying from one to the other, why don't you do it in SQL?

You can create a Synonym within one database pointing at a table, view or stored proc on another database (on another server). You can then insert into this synonym just like you could into a table in the same db.

http://www.developer.com/db/article.php/3613301/Using-Synonyms-in-SQL-Server-2005.htm

Upvotes: -1

Related Questions