reading one oracle database writing to another remote oracle database

Question

c# code: i have 2 oracle databases one is remotely, i am selecting values from my tables in my local oracle database: the query returns 8 columns: i want to write the data in a remote oracle table having different column names and attributes:i want to also make sure that only new row values are inserted:the table structure of my remote database is different from the columns returned by my select query.

my select statement from different table query returns the following: columns: customer_name,customer_id, customer_dep_number,mobile_number, payment_amount, vehicle_model, collection_point, transaction_date

the structure of the remote table is as follows: TABLE vehicle_bought

BUYER_ID NUMBER NOT NULL,

BUYER_NAME VARCHAR2(100 BYTE) NOT NULL,

BUYER_NAT_ID VARCHAR2(25 BYTE) DEFAULT 'N/A' NOT NULL,

BUYER_VEHICLE_DESCRIPTION VARCHAR2(2000 BYTE) NOT NULL,

BUYER_STATUS VARCHAR2(1 BYTE) DEFAULT 'U' NOT NULL,

VEHICLE_AMOUNT NUMBER NOT NULL,

CREATED_BY NUMBER,

TRANS_CREATION_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

RECEIPT_CREATED VARCHAR2(1 BYTE) DEFAULT 'N',

MOBILE_NUM VARCHAR2(50 BYTE),

VEHICLE_COLLECTION_POINT VARCHAR2(100 BYTE),

RMA_FLAG VARCHAR2(1 BYTE) DEFAULT 'N',

)

the fields i want to read and insert to are as follows: customer_name = BUYER_NAME, customer_id = BUYER_ID

customer_dep_number = BUYER_NAT_ID

mobile_number = MOBILE_NUM

payment_amount = VEHICLE_AMOUNT

vehicle_model = BUYER_VEHICLE_DESCRIPTION

collection_point = VEHICLE_COLLECTION_POINT

transaction_date = TRANS_CREATION_DATE

very urgent language c# please :souvenir promised to whoever helps out.

Answer 1

Quick and dirty solution:

1. Create a database link in the remote database (the destination), that connects to the local database (the source)

2. Run the insert across the database link from the remote database, i.e.

   INSERT INTO desttable (customer_name,
                          customer_id,
                          customer_dep_number,
                          mobile_number, ...)
   SELECT s.buyer_name,
          s.buyer_id,
          s.buyer_nat_id,
          s.mobile_num,
          ...
   FROM   sourcetable@dblink s
   WHERE NOT EXISTS (
      SELECT null FROM desttable
      WHERE desttable.buyer_id = s.buyer_id
   );
   

Alternative: a better solution might use a MERGE instead