从命令行将数据从 csv 文件加载到 oracle 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21279194/
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
Load data from csv file to oracle table from command line
提问by jasim
I tried with sql loader.The thing is the table needs to be empty for data loading.Is there any way to do data upload without truncating the table.The CSV data need to be appended in the table.i am using oracle 11g.
我尝试使用sql loader。问题是数据加载时表需要为空。有没有办法在不截断表的情况下进行数据上传。需要将CSV数据附加到表中。我使用的是oracle 11g。
回答by Alex Poole
The SQL*Loader documentationsays:
When you are loading a table, you can use the
INTO TABLEclause to specify a table-specific loading method (INSERT,APPEND,REPLACE, orTRUNCATE) that applies only to that table. That method overrides the global table-loading method. The global table-loading method isINSERT, by default, unless a different method was specified before anyINTO TABLEclauses.
当你装载一个表时，你可以使用该
INTO TABLE子句指定特定的表装载方法（INSERT，APPEND，REPLACE，或TRUNCATE），其仅适用于表。该方法覆盖全局表加载方法。INSERT默认情况下，全局表加载方法是，除非在任何INTO TABLE子句之前指定了不同的方法。
So by default your table load will be in INSERTmode, which does require the table to be empty.
因此，默认情况下，您的表加载将处于INSERT模式，这确实需要表为空。
The documentation also explains how to load data into a non-empty table; in your case you want to preserve the existing data:
该文档还解释了如何将数据加载到非空表中；在您的情况下，您要保留现有数据：
APPEND
If data already exists in the table, then SQL*Loader appends the new rows to it. If data does not already exist, then the new rows are simply loaded. You must haveSELECTprivilege to use theAPPENDoption.
APPEND
如果数据已存在于表中，则 SQL*Loader 将新行追加到该表中。如果数据尚不存在，则只需加载新行。您必须具有SELECT使用该APPEND选项的权限。
So your control file will need to say something like this (as shown in their example):
所以你的控制文件需要这样说（如他们的例子所示）：
LOAD DATA
INFILE 'my_file.dat'
BADFILE 'my_file.bad'
DISCARDFILE 'my_file.dsc'
APPEND
INTO TABLE my_table
...
You could also consider using the new CSV data as an external tableand inserting to your real table from that, which might be a bit more flexible.
您还可以考虑使用新的 CSV 数据作为外部表并从中插入到您的真实表中，这可能会更灵活一些。
