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
Best Answer
Not using a built in command, but...
If an
UPDATE
really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:
If you cannot query the remote database, you can update every row like this: