Update oracle sql database from CSV

csvimportoracleoracle-11g

I tried google at first but no luck.
Is it possible to update tables from csv file?
I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the edited rows through that csv file from a client. I don't want to import the whole file as the client already has a mirror table, I just would like to update the data from what it is in the csv file.
Is this possible?

If not what would be the best approach?

Best Answer

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
Related Question