Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Posts
    7

    Unanswered: 2 database problem

    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?

    Thank you.

  2. #2
    Join Date
    Sep 2001
    Posts
    2
    Hi,


    Having Oracle generate your SQL insert statements using a select statement is quite easy.

    Example:
    CREATE TABLE localTest(
    id NUMBER,
    value1 VARCHAR2(10),
    value2 VARCHAR2(10));


    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.

    Hope this helps!

    Best Regards,
    C. Paulsen


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •