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
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;
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!