Almost done!

[nedpan]

Hi again,

Thank you for the information.
I am still having some problems. Will give you the details again - maybe I missed something the first time.

I have 2 databases.
DB1 & DB2.
There is data that is going once a week into DB1. This data i have to capture and populate a table (table 'A') in DB2.
Once table 'A' is populated I need to store the old data into tables in DB2. For example:
given_name in table'A' used to be 'y', but after the update is now'x'. I have to take 'y' and put it into another table so that it is stored and accessible if needed.

What I have so far done:
I have a dblink between DB1 and DB2.
I have a temp table called 'B' that has the fields from the six tables in DB1 that I want to take the data from.

My problem:
I do not know how to extract ONLY the data I need so that I can populate table 'B' in DB1(my temp table).
For example;
CLIENT_ID NOT NULL VARCHAR2(9)
DATE_OF_BIRTH NOT NULL DATE
SEX NOT NULL VARCHAR2(1)
MARITAL_STATUS VARCHAR2(1)
SIN VARCHAR2(9)
REGION_CODE NOT NULL VARCHAR2(2)
ZONE_CODE VARCHAR2(1)
BASE_FACILITY_CODE VARCHAR2(2)
COMM_CODE VARCHAR2(2)
ADDRESS_CITY VARCHAR2(30)
ADDRESS_STREET VARCHAR2(30)
PROVINCE VARCHAR2(2)
POSTAL_CODE VARCHAR2(6)
CLIENT_TYPE NOT NULL VARCHAR2(1)
BC_SENT_FLAG VARCHAR2(1)
TRANS_FLAG VARCHAR2(1)
UPDATE_DATE DATE
UPDATE_ID VARCHAR2(30)
API VARCHAR2(1)
INNU_NUMBER VARCHAR2(5)
INSERT_DATE NOT NULL VARCHAR2(14)
PILOT_ID VARCHAR2(3)
NWT_COMM_CODE VARCHAR2(4)

Out of the above table I only need to get client_id, sex,region_code,province,client_type, api,pilot_id.
I have five more tables similar to the one above that I need to do the same thing with.
If I get pass this problem then the only other one is once my temp table ('B') is populated how do I transfer the data from table 'B' over to table'A'. Keep in mind 'A' is in DB2 and 'B' is in DB1.

I trully hope you can assist me in completing my task.
Once again thank you very much for you time.

[pipo]

I hope I understand your pb correctly :

insert into A
select client_id, sex,region_code,province,client_type, api,pilot_id
from b@db_link;