使用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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-743755/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-743755/