Oracle – Moving Data Between Identical Table Structures

The data dictionary will only show what it can.

Users can design their schema with or without referential integrity defined in the actual database.

When you import a data dictionary, using SQL Developer, to a new or existing relational data model, the foreign key constraints will be shown by default - if they are there to begin with.

Now, specific to the tool, it CAN attemp to infer any relationships. It does this by looking for common column names sprinkled around your tables, like an 'XYZ_ID' column, that happens to be a PRIMARY KEY in a driving table.

I have step-by-step instructions for this here, on my blog.

I would suggest you to make use of External Tables. You can create an external table on your CSV file using ORACLE_LOADER driver and then update your existing table with data in your external table using DML (MERGE for example).

Consult Oracle Utilities Guide for detailed info.

What follows is my sample of how you can update tables from flat files.

First you create the directory where your flat file will be located:

SQL> create directory ext_tab_dir as '/home/oracle/ora_load';
Directory created.

SQL> grant read, write on directory ext_tab_dir to spongebob;
Grant succeeded.

Then you create your external table:

SQL> CREATE TABLE emp_load (empid          number(3), 
                            first_name     CHAR(15), 
                            last_name      CHAR(20), 
                            year_of_birth  CHAR(4))
       ORGANIZATION EXTERNAL (
         TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
         ACCESS PARAMETERS (RECORDS FIXED 21 FIELDS (
           empid          char(1),
           first_name     CHAR(7),
           last_name      CHAR(8),
           year_of_birth  CHAR(4)))
         LOCATION ('info.dat')
       );

We'll assume that you already have the table that you want to update with data from the flat file:

SQL> CREATE TABLE emp (empid         number(3), 
                      first_name     CHAR(15), 
                      last_name      CHAR(20), 
                      year_of_birth  CHAR(4));
SQL> insert into emp values(1, 'SpongeBob', 'SquarePants', '1997');
SQL> insert into emp values(2, 'Patrick',   'Star',        '1997');
SQL> insert into emp values(3, 'Squidward', 'Tentacles',   '1997');

(Here, I just loaded it with sample data.)

Now that's what your info.dat contains, for example:

[oracle@oca ~]$ cat ora_load/info.dat
1Alvin  Tolliver1976
2KennethBaer    1963
3Mary   Dube    1973
4NandiniShastri 1985

At this point you will just update your existing table with above data from the external table:

SQL> merge into emp e1
       using emp_load e2
       on (e1.empid = e2.empid)
       when matched then update set e1.first_name    = e2.first_name,
                                    e1.last_name     = e2.last_name
       when not matched then insert
         values (e2.empid, e2.first_name, e2.last_name, e2.year_of_birth);

Here's what you'll get as a result:

SQL> select * from emp;
     EMPID FIRST_NAME      LAST_NAME            YEAR
---------- --------------- -------------------- ----
         1 Alvin           Tolliver             1997
         2 Kenneth         Baer                 1997
         3 Mary            Dube                 1997
         4 Nandini         Shastri              1985