transfer data between two Oracle DBs through VB.NET (or ORA Directly..

[CraigBest]

Hi Folks

I am writing a Visual Basic .NET app the has two elements, a base app and a mobile app. From time to time, the mobile unit will need to copy data up to a master database.

My plan is to have two separate ODBC connections, but have them share a dataset. The Mobile unit will fill a datatable in the dataset using a standard "SELECT * FROM TABLE" type statement from DB1. Then I would like to use some sort of a statement like "INSERT INTO TABLE (SELECT * FROM DATASET/DATATABLE)" into DB2.

For information purposes, DB1 is an Oracle Lite installation, DB2 is a standard Oracle 9i Server installation. For the tables that need to share data the schema is the same.

My problem is I don't know exactly how to phrase the second SQL statement to get the data from a datatable instead of a actual table or view. I remember reading some time back that it was possible in Visual Studio 2005 to set up a dataset to behave like a virtual data table accessible by SQL commands but I can't find any reference now. Can someone tell me what the proper phrasing is to do this? Or is there a better way to do this that I may not be aware of?

BTW I asked this first in the VB.NET 2005 forum and they suggested I come here for help...

Thanks

Thanks for any help you can provide.

Craig


CraigHartz

 

[Turkbear]

Hi,
It might be far simpler to create a database link in the 9i instance that connects to the mobile one..Build a Sql statement that uses that link for copying the data:

"Insert into db2.table_name select * from db1.table_name@databaselink"

Use 2 Connections; OleDb (ADO) may be better than ODBC to insure full compatibility ( Using Oracle's .NET Provider)





To Paraphrase:"The Help you get is proportional to the Help you give.."