I've 2 databases, one at the branch and one at the HQ. At HQ, it is Oracle and at the branches, they are access database.
What I would like it to do is, every night, the branches will connect to the HQ and :
1) get any new inventory and download it.
2) copy that day's sales to the HQ for further processing.
My problem is, how do I copy data from Oracle to the access database? Do I execute SQL statement and return the data to a recordset, and then open another recordset connected to the access database and copy from one recordset to the other? Is there any other way, such as making Oracle create the neccessary SQL statement for me to insert into access database?
Having Oracle generate your SQL insert statements using a select statement is quite easy.
CREATE TABLE localTest(
To create insert statements to insert all rows from the localTest table into the remoteTest table with same definition:
SELECT 'INSERT INTO remoteTest VALUES('
||id||','''||value1||''','''||value2||''');' FROM localTest;
- Notice escaping for the quotes around strings.
- Remember to use NVL for columns where NULLS are possible.
Another way of doing this would be to export the table from Oracle using exp and then pull the insert statements from the export file. I have never done this to move data between different types of DBs - only from one Oracle db to another.