Need to copy data between two databases...

[CraigBest]

Hi Folks

I am writing an 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 connections, but have them share a dataset. The Mobile unit will fill a datatable in the dataset using a "SELECT * FROM DATABASE1.TABLE" type statement. Then I would like to use an "INSERT INTO DATABASE2.MASTERTABLE (SELECT * FROM DATASET/DATATABLE)".

My problem is I don't know exactly how to phrase the second statement, to get the data from a datatable instead of a actual table or view. 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?

Thanks for any help you can provide.

Craig


CraigHartz

 

[Dashley]

Craig,

Is the master DB at a website? Can you describe the situation some more. Where is the main DB in contrast to the handhelds.

 

[SHelton]

If the two databases have tables with the same schema, you can load an empty DataSet from db2, then merge in a populated DataSet from db1. You can then update the DataSet to db2.

 

[CraigBest]

Hi Dashley

The primary DB (Base) is an Oracle server in our main office. The mobile DB is an Oracle Lite on a laptop that will be mounted in a truck. The night supervisor drives around in the truck and collects data on our properties, like a night watchman. When his shift is over he drives to our HQ building and initiate a transfer in the program. All data on the laptop is to be inserted into the primary DB as a part of the transfer, and lookup tables on the laptop will be refreshed from the Base server. The connection to the Base DB is over a wireless connection, we have already tested this and it works well.

Hope this helps!

Craig


CraigHartz

 

[CraigBest]

SHelton,

Thanks but I don't quite follow. Can you elaborate on the process? I think I'm missing something.

Craig


CraigHartz

 

[CraigBest]

Sorry, yes the tables in each DB have the same schema.


CraigHartz

 

[CraigBest]

Some more background,

The primary DB is not accessible through a website. Connection is made over the corporate network with an Oracle DB driver. the Mobile unit uses a Personal Oracle Lite installation, and the app connects to it through ODBC.

As an example / test, I have loaded a datatable (dtUsers) with the complete contents of one of my smaller tables from the Primary DB -- "SELECT * FROM USERS". Now I want to take the contents of dtUsers and copy it to a currently empty table on the mobile computer of the same name and schema. I had thought to use an "INSERT INTO Users (SELECT * FROM dtUsers)", but of course I don't know how to reference dtUsers in this way.

Hope someone can help!!!

Craig




CraigHartz

 

[Dashley]

craig,

Ok I understand. I'm always passing through webservices etc.. So I can't really offer any good advice. I know in Sqlserver we can referene another DB by addresing with an absolute address format like select * from database.owner.table where..

You might want to give the oraclle db forum a chance to look at this one. its forum forum759

-dan

 

[CraigBest]

Dan,

Thanks I'll try over there. In the meantime if anyone else here has any ideas...


Craig


CraigHartz