想把一个文件里面的数据同时导到两张表控制文件如下:Load DATA
INFILE 'test_direct.dat'
discardfile 'test_direct.dsc'
append
into table testf
when empid != ''
fields terminated by '|'
(
empid integer EXTERNAL,
empname char(20),
salary integer EXTERNAL
)
into table testg
when empid != ''
fields terminated by '|'
(
empid integer EXTERNAL,
empname char(20),
salary integer EXTERNAL
)
数据文件如下:2010|zz|5600|
2011|gg|5700|
2012|mm|5800|
2018|sf|15800|
2020|sd|75800|
2021|ak|65800|
sqlldr test/test@test control=test.ctv log=test.log结果不成功,日志如下:
Table TESTF:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table TESTG:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
6 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
INFILE 'test_direct.dat'
discardfile 'test_direct.dsc'
append
into table testf
when empid != ''
fields terminated by '|'
(
empid integer EXTERNAL,
empname char(20),
salary integer EXTERNAL
)
into table testg
when empid != ''
fields terminated by '|'
(
empid integer EXTERNAL,
empname char(20),
salary integer EXTERNAL
)
数据文件如下:2010|zz|5600|
2011|gg|5700|
2012|mm|5800|
2018|sf|15800|
2020|sd|75800|
2021|ak|65800|
sqlldr test/test@test control=test.ctv log=test.log结果不成功,日志如下:
Table TESTF:
6 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table TESTG:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
6 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
create table testf
(
EMPID NUMBER(4),
EMPNAME VARCHAR2(20),
SALARY NUMBER(16,2)
);create table testg as select * from testf;从日志来看,这些记录只被导进了一张表我试过将when去掉,结果发现两张表都没导进去不知道有哪位大是能解释一下为什么会出现这样的情况,要怎么修改控制文件才能达到目的?
LOAD DATAINFILE 'filename.txt'appendINTO TABLE table1WHEN col1!= ' '
fields terminated by " "
TRAILING NULLCOLS
(col1,col2)INTO TABLE table2WHEN col2!= ' '
(col1 POSITION(1:11) CHAR,
col2 POSITION(12:18) CHAR)以上方法可以导入2张表中。要求数据长度一致。
同病相怜,我也是要这样导入,但是表的字段表比较多而且长度不确定。
结果发现又不能同时导入两张表了
和
WHEN col2!= ' '