如何将CSV数据加载到具有比csv文件更多列的Oracle表中？

I have a .csv file that contains data for only certain columns in a table. How do I import that .csv into the table leaving the other columns intact (or setting the NULL value)?

我有一个.csv文件,其中只包含表中某些列的数据。如何将.csv导入表中而保持其他列不变(或设置NULL值)?

Initially the table had the same columns as the .csv file and I was using this SQL loader:

最初,该表与.csv文件具有相同的列,我使用的是这个SQL加载器:

LOAD DATA
INFILE '"Path"/file.csv'
APPEND
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
(
    COLUMN1,
    COLUMN2,
    COLUMN3,
    COLUMN4,
    COLUMN5
)

but then I had to add some other columns in table that need to be updated with data from other tables, and I want to update them (using a trigger) when inserting the data from the .csv for the initial columns. So how do I import in table only the columns in .csv?

但后来我不得不在表中添加一些需要使用其他表中的数据进行更新的其他列,并且我想在从.csv中插入初始列的数据时更新它们(使用触发器)。那么如何在表中仅导入.csv中的列?

2 个解决方案

#1

There is nothing you need to do.

你无需做任何事情。

Only the column values from the file will be sent.

仅发送文件中的列值。

The other columns will get their default value (typically NULL, but that depends on the column definition) when the rows are inserted.

插入行时,其他列将获取其默认值(通常为NULL,但这取决于列定义)。

Not sure what you mean with leaving the other columns intact, as SQL*Loader will only insert new rows, there is nothing to be "left intact".

不确定你的意思是保持其他列不变,因为SQL * Loader只会插入新的行,没有什么是“保持原样”。

#2

Also you can use awk.

你也可以使用awk。

Here is a detailed example on how to compose SQL queries based on values from CSV file.

以下是有关如何基于CSV文件中的值组成SQL查询的详细示例。

SQL> create table MY_TABLE(a varchar2(100), b varchar2(100));

[oracle@myora ~]$ tail Some_Input_CSV_file
Some Data A 1,Some Data B 1
Some Data A 2,Some Data B 2
Some Data A 3,Some Data B 3
Some Data A 4,Some Data B 4
Some Data A 5,Some Data B 5
Some Data A 6,Some Data B 6
Some Data A 7,Some Data B 7
Some Data A 8,Some Data B 8
Some Data A 9,Some Data B 9
Some Data A 10,Some Data B 10
[oracle@myora ~]$

[oracle@myora ~]$ cat Some_Input_CSV_file | awk -F, ‘ { printf(“insert into MY_TABLE values(trim(\x27%s\x27), trim(\x27%s\x27));\n”, $1, $2); } ‘ > RunMe.sql

[oracle@myora ~]$ tail RunMe.sql
insert into MY_TABLE values(trim(‘Some Data A 1′), trim(‘Some Data B 1′));
insert into MY_TABLE values(trim(‘Some Data A 2′), trim(‘Some Data B 2′));
insert into MY_TABLE values(trim(‘Some Data A 3′), trim(‘Some Data B 3′));
insert into MY_TABLE values(trim(‘Some Data A 4′), trim(‘Some Data B 4′));
insert into MY_TABLE values(trim(‘Some Data A 5′), trim(‘Some Data B 5′));
insert into MY_TABLE values(trim(‘Some Data A 6′), trim(‘Some Data B 6′));
insert into MY_TABLE values(trim(‘Some Data A 7′), trim(‘Some Data B 7′));
insert into MY_TABLE values(trim(‘Some Data A 8′), trim(‘Some Data B 8′));
insert into MY_TABLE values(trim(‘Some Data A 9′), trim(‘Some Data B 9′));
insert into MY_TABLE values(trim(‘Some Data A 10′), trim(‘Some Data B 10′));
[oracle@myora ~]$

[oracle@myora ~]$ sqlplus myuser/mypwd@myinst

SQL> @RunMe.sql

…

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

SQL> commit;

Commit complete.

SQL> exit

#1