Update oracle sql database from CSV

Question

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?

Answer 1

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