How to import data from one table to another table in different Databases

To import data from one table to another table in different database you have to create a database link.

Source:

Database: 192.168.0.16/GLOBAL

Schema: GLOBBASE

Table: GLOB_COUNTRY

Destination:

Database: 192.168.0.36/LOCAL

Schema: LOCALBASE

Table: LOCAL_COUNTRY

Connect to GLOBAL database and view data form GLOB_COUNTRY

select * from GLOB_COUNTRY;

CODE    NAME           gC_CODE

1       Afghanistan    af

2       Albania        al

3       Algeria        dz

10      Argentina      ar

14      Australia      au

15      Austria        at

16      Azerbaijan     az

19      Bahrain        bh

…

Connect to LOCAL database.

Create table same as GLOB_COUNTRY table.

CREATE TABLE LOCAL_COUNTRY

    (code                           FLOAT(126),

    name                           VARCHAR2(255),

    lc_code                         VARCHAR2(255))

/

Create database link from the LOCAL database to GLOBAL database.

create database link glocal_link connect to GLOBBASE identified by password using '192.168.0.16/GLOBAL';

Insert data using database link

insert into LOCAL_COUNTRY

select * from GLOB_COUNTRY@glocal_link;

commit;

Select data from new table

select * from LOCAL_COUNTRY;

CODE    NAME           lC_CODE

1       Afghanistan    af

2       Albania        al

3       Algeria        dz

10      Argentina      ar

14      Australia      au

15      Austria        at

16      Azerbaijan     az

19      Bahrain        bh

….

Data have been load to the LOCAL_COUNTRY table.