使用SQLLOADER將每行資料根據條件插入不同表

SQL*LOADER支援將每行資料資料（物理行）根據條件插入不同表，本文進行測試驗證。

1,測試：
1.0測試表
create table multi_line(
x1 varchar2(100),x2 varchar2(100),x3 varchar2(100));

create table multi_line2(
x1 varchar2(100),x2 varchar2(100),x3 varchar2(100));

2.1測試
test1:
cat normal_line.ctl

LOAD DATA
INFILE *
INTO TABLE multi_line TRUNCATE
when recid = '1'
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( recid filler,
  x1,
  x2,
  x3
)
INTO TABLE multi_line2 TRUNCATE
when recid = '2'
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( recid filler,
  x1,
  x2,
  x3
)

BEGINDATA
1,a1,b1,c1
2,a2,b2,c2
1,a3,b3,c3
2,a4,b4,c4
1,a5,b5,c5

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
SQL> select * from multi_line;
a1         b1         c1
a3         b3         c3
a5         b5         c5

SQL> select * from multi_line2;

no rows selected

--&gtno data inserted!

test2:
增加 position(1),設定條件起點為行首：
cat normal_line.ctl

LOAD DATA
INFILE *
INTO TABLE multi_line TRUNCATE
when recid = '1'
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( recid filler,
  x1,
  x2,
  x3
)
INTO TABLE multi_line2 TRUNCATE
when recid = '2'
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( recid filler position(1),
  x1,
  x2,
  x3
)

BEGINDATA
1,a1,b1,c1
2,a2,b2,c2
1,a3,b3,c3
2,a4,b4,c4
1,a5,b5,c5

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
SQL> select * from multi_line;
a1         b1         c1
a3         b3         c3
a5         b5         c5

SQL> select * from multi_line2;
a2         b2         c2
a4         b4         c4