How to copy a single row from 1 table to another in a different schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to copy a single row from 1 table to another in a different schema

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    Here's the dilemma. I have 2 databases structured exactly the same DB1 AND DB2.
    Both databases have a schema called EMPOWER where all the tables are housed. There are more than 500 tables and all tables are linked by their primary key of LNKEY.

    Even though these 2 dbs have the same structure, the data they contain is not identical. I have specific records that I need to copy from DB1 to DB2.

    Currently I have a stored procedure that copies one loan record to a new loan record in the same database.
    i.e. EXECUTE EMPOWER.COPYLOAN(‘111SOURCE’,’222TARGET’);

    here’s the sql for the stored procedure

    CREATE PROCEDURE EMPOWER.COPYLOAN
    (FROMNUM IN VARCHAR2, TONUM IN VARCHAR2) AS
    BEGIN
    INSERT INTO EMPOWER.ADD_CRED (LNKEY,WHICHBORR,IDX,ADD_CRED_ALT_NAME)
    SELECT TONUM,WHICHBORR,IDX,ADD_CRED_ALT_NAME FROM EMPOWER.ADD_CRED WHERE LNKEY = FROMNUM;
    INSERT INTO EMPOWER.ALIMONY (LNKEY,ALIMONY_PMT,ALIMONY_NUMPMT,ALIMONY_NAME)
    SELECT TONUM,ALIMONY_PMT,ALIMONY_NUMPMT,ALIMONY_NAME FROM EMPOWER.ALIMONY WHERE LNKEY = FROMNUM;
    ...
    did the above for all tables in that schema
    ...
    INSERT INTO EMPOWER.ARMDISC (LNKEY,ARM_PROGRAM,INDEX_INFO)
    SELECT TONUM,ARM_PROGRAM,INDEX_INFO FROM EMPOWER.ARMDISC WHERE LNKEY = FROMNUM;
    END;

    But this time, I want to copy these specific records from DB1 into DB2.
    DB2 has also a schema called EMPOWER with all the underlying tables.

    These 2 databases are on 2 separate boxes.
    Your help will be very much appreciated!

    Robel Girma

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    cant you use db link?

    or copy command in sqlplus?

  3. #3
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    Yes, that's an option. Can anyone point me to some documentations I can refer to for using dblinks ?
    Or if you can, some examples on how this works.

    Thanks for you help

    Robel Girma

  4. #4
    Join Date
    Aug 2001
    Posts
    111
    Dblinks are a start...
    Have you thought about using snapshots (v8) or materialised views (8i,9) to do this as well....depending on whether you want the whole table or even schema replicated.

    All that is needed for a database link is to have a valid tnsnames alias to point to either db1 or db2.
    The Oracle documentation on links is good and explains the concept well.

    Why i suggested replication via snapshots or materialised views is once this is setup Oracle can manage the whole process via job scheduling.
    Does the requirement for the application require a loan record in one database (db1) be replicated in database (db2) if so, i suggest reading the chapters in the documentation on basic replication. You already have the building blocks (primary keys on all tables) and the same schema name, so setting this up will be straightforward.

    Have Fun
    Performance... Push the envelope!

  5. #5
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    Thanks you all for your input. I'll implement the suggested db link method. I think this sounds like what I'm looking for.

    Robel

Posting Permissions

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



Click Here to Expand Forum to Full Width