SQL 使用 CSV 文件中的值更新 Oracle 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1952714/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update Oracle table with values from CSV file
提问by Thomas L?tzer
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?
我有一个包含 ID 和其他几个列的 CSV 文件。我在 oracle 中也有一个表，其中 ID 标识一行。我怎样才能最好地用 CSV 文件中的值替换表中的值，同时保持其他列的原样？
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.
这必须使用 oracle 本身中可用的工具（即 PL/SQL 或 SQL 脚本）来完成，我不能使用“真正的”脚本语言（Python，...）或“真正的”程序。
Thanks, Thomas
谢谢，托马斯
回答by CMG
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.
查看 Oracle 文档中的 EXTERNAL TABLES。您可以将 csv 文件复制到 Oracle 服务器机器上，并让 Oracle 将其显示为“普通”表，您可以在该表上运行查询。
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.
外部表对于处理这样的数据加载非常有用。
回答by skaffman
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.
用于将 CSV 类型的数据文件加载到数据库中的“标准”Oracle 工具是SQLLoader。它通常用于插入记录，但可以将控制文件配置为运行更新，如果您愿意的话。
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.
它不是世界上最容易使用的工具（阅读“它是一个痛苦的屁股”），但它是标准工具包的一部分，并且可以完成工作。
回答by Philip Schlump
Use SQL*Loader
使用 SQL*Loader
sqlldr username@server/password control=load_csv.ctl
The load_csv.ctl file
load_csv.ctl 文件
load data
infile '/path/to/mydata.csv'
into table mydatatable
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
where /path/to/mydata.csvis 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.
其中/path/to/mydata.csv是您需要加载的 CSV 文件的路径，在 Windows 上类似于C:\data\mydata.csv。表名是mydatatable。这些列按它们出现在最后一行的 csv 文件中的顺序列出。
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.
除非您有大量数据，否则这是获取数据的最容易维护的方式。系统。
回答by Bob Jarvis - Reinstate Monica
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:
另一种选择是逐行读入文件，使用诸如 REGEXP_REPLACE 之类的方法解析该行中的字段，然后更新相应的行。您可以解析逗号分隔的字符串，如下所示：
SELECT TRIM(REGEXP_REPLACE(strLine, '(.*),.*,.*', '')),
TRIM(REGEXP_REPLACE(strLine, '.*,(.*),.*', '')),
TRIM(REGEXP_REPLACE(strLine, '.*,.*,(.*)', ''))
INTO strID, strField1, strField2 FROM DUAL;
This is useful if your site doesn't allow the use of external tables.
如果您的站点不允许使用外部表，这将非常有用。
Share and enjoy.
分享和享受。
回答by Naveen Yadav
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
首先创建一个表并将您所有的CSV数据放入该表中，然后写入一个游标以使用与ID对应的CSV创建的表来更新您的oracle表
ex
前任
create table t
and then import your CSV data into this table.
然后将您的 CSV 数据导入到此表中。
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
我认为它会起作用
