SQL 加载器：从 1 个控制文件和 1 个 CSV 加载到 2 个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/705255/
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
SQL Loader : Load into 2 Tables from 1 controlfile and 1 CSV
提问by
Table T1 Structure:col1 number,col2 number
表T1结构：col1编号，col2编号
Table T2 Structure: col1 number,col2 number,col3 number
表T2结构：col1编号，col2编号，col3编号
csv file:
.csv 文件：
row1:1,2,3,4,5,6
row2:1,2,3,4,5,6
I want data in Table T1 as:
我希望表 T1 中的数据为：
col1 col2
===== ====
1 3
1 3
I want data in Table T2 as:
我希望表 T2 中的数据为：
col1 col2 col3
===== ==== ====
1 3 5
1 3 5
following control file is not working
以下控制文件不起作用
load data
infile *
insert into table T1 fields terminated by ',' TRAILING NULLCOLS
(col1,fill1 filler,col2)
insert into table T2 fields terminated by ',' TRAILING NULLCOLS
(col1,fill2 filler,col2,fill3 filler,col3)
begindata
1,2,3,4,5,6
1,2,3,4,5,6
Please help me to solve this problem.
请帮我解决这个问题。
回答by Neil Kodner
This does work but I don't like this solution. I prefer to use external tables in these situations. The short answer is you were having problems because SQLLDR doesn't automatically re-scan the data on multiple row imports. A snippet from the documentation follows.
这确实有效，但我不喜欢这个解决方案。在这些情况下，我更喜欢使用外部表。简短的回答是您遇到了问题，因为 SQLLDR 不会自动重新扫描多行导入的数据。下面是文档中的一个片段。
Using POSITION with Multiple Table Loads
对多个表加载使用 POSITION
In a multiple table load, you specify multiple INTO TABLE clauses. When you specify POSITION() for the first column of the first table, the position is calculated relative to the beginning of the logical record. When you specify POSITION() for the first column of subsequent tables, the position is calculated relative to the last column of the last table loaded.
在多表加载中，您指定多个 INTO TABLE 子句。当您为第一个表的第一列指定 POSITION( ) 时，将相对于逻辑记录的开头计算位置。当您为后续表的第一列指定 POSITION() 时，将相对于加载的最后一个表的最后一列计算位置。
Thus, when a subsequent INTO TABLE clause begins, the position is not set to the beginning of the logical record automatically. This allows multiple INTO TABLE clauses to process different parts of the same physical record. For an example, see Extracting Multiple Logical Records.
因此，当后续的 INTO TABLE 子句开始时，位置不会自动设置为逻辑记录的开头。这允许多个 INTO TABLE 子句处理同一物理记录的不同部分。有关示例，请参阅提取多个逻辑记录。
A logical record might contain data for one of two tables, but not both. In this case, you would reset POSITION. Instead of omitting the position specification or using POSITION(*+n) for the first field in the INTO TABLE clause, use POSITION(1) or POSITION(n).
一个逻辑记录可能包含两个表之一的数据，但不能同时包含两个表的数据。在这种情况下，您将重置 POSITION。对于 INTO TABLE 子句中的第一个字段，不要省略位置说明或使用 POSITION(*+n)，而是使用 POSITION(1) 或 POSITION(n)。
load data
infile *
truncate
into table T1
fields terminated by ',' trailing nullcols
( col1
, fill1 filler
, col2 )
into table T2
fields terminated by ',' trailing nullcols
( col1 position(1)
, filler filler
, col2
, filler2 filler
, col3 )
BEGINDATA
1,2,3,4,5,6
1,2,3,4,5,6
SQL> select * From t1;
COL1 COL2
---------- ----------
1 3
1 3
SQL> select* From t2;
COL1 COL2 COL3
---------- ---------- ----------
1 3 5
1 3 5
回答by Sourabh Sharma
Try this
尝试这个
LOAD DATA
INFILE * "STR '|EndRec|'"
INSERT INTO TABLE T1 FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(COL1, FILL2 FILLER, COL2, FILL4 FILLER, FILL5 FILLER, FILL6 FILLER)
INSERT INTO TABLE T2 FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(COL1 POSITION(1), FILL2 FILLER, COL2, FILL3 FILLER, COL3, FILL6 FILLER)
BEGINDATA
1,2,3,4,5,6|EndRec|1,2,3,4,5,6|EndRec|
回答by Quassnoi
Try this:
尝试这个：
LOAD DATA
INFILE *
INSERT INTO TABLE T1 FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(COL1, FILL2 FILLER, COL2, FILL4 FILLER, FILL5 FILLER, FILL6 FILLER)
INSERT INTO TABLE T2 FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(COL1, FILL2 FILLER, COL2, FILL3 FILLER, COL3, FILL6 FILLER)
BEGINDATA
1,2,3,4,5,6
1,2,3,4,5,6
