Update Oracle table with values from CSV file

问题:

I have a CSV file which contains an ID and several other columns. I also have a table in oracle where the ID identifies a row. How can I best replace the values that are in the table with the values in the CSV file while keeping the other columns the way they were before?

This has to be done with tools available in oracle itself (i.e. PL/SQL or SQL scripts), I can not use a "real" scripting language (Python, ...) or a "real" program.

Thanks, Thomas

回答1:

Look at EXTERNAL TABLES in the Oracle doc. You can copy the csv file onto the Oracle server box and get Oracle to present it as a "normal" table on which you can run queries.

Then the problem just becomes one of copying data from one table to another.

External tables are really very useful for handling data loads like this.

回答2:

The "standard" Oracle tool for loading CSV-type datafiles into the database is SQLLoader. It's normally used to insert records, but the control file can be configured to run an update instead, if that's what you so desire.

It's not the easiest tool in the world to use (read "it's a pain in the arse"), but it's part of the standard toolkit, and it does the job.

回答3:

Another option is to read in the file line-by-line, parse out the fields in the line using something like REGEXP_REPLACE, and update the appropriate rows. You can parse a comma-delimited string like the following:

SELECT TRIM(REGEXP_REPLACE(strLine, '(.*),.*,.*', '\1')),
       TRIM(REGEXP_REPLACE(strLine, '.*,(.*),.*', '\1')),
       TRIM(REGEXP_REPLACE(strLine, '.*,.*,(.*)', '\1'))
  INTO strID, strField1, strField2      FROM DUAL;

This is useful if your site doesn't allow the use of external tables.

Share and enjoy.

回答4:

Use SQL*Loader

sqlldr username@server/password control=load_csv.ctl

The load_csv.ctl file

load data
 infile '/path/to/mydata.csv'
 into table mydatatable
 fields terminated by "," optionally enclosed by '"'          
 ( empno, empname, sal, deptno )

where /path/to/mydata.csv is the path to the CSV file that you need to load, on Windows something like C:\data\mydata.csv. The tablename is mydatatable. The columns are listed in order that they apear in the csv file on the last line.

Unless you have a very large volume of data this is the easiest to maintain way of getting the data in. If the data needs to be loaded on a regular basis this can be worked into a shell script and run by CRON on a U*NX system.

回答5:

First create a table and put all your CSV data into that table,and then write a cursor to update your oracle table with the CSV created table corresponding to IDs

ex

create table t

and then import your CSV data into this table.

now write a cursor

declare
cursor c1 is
select * from t1;
begin
update Oracle table
set
t1.Column=t2.column
where t1.id=t2.id;

i think it will work