以下是一个将一份数据文件入到两张表的例子,注意第二张表字段的写法。
控制文件sqlldr.ctl
load data
infile 'test.csv'
preserve blanks
into table t_test_a
truncate
when flag='1'
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
flag filler,
col1 constant 'z',
col2 nullif col2='2',
col3,
col4 defaultif col3='3',
col5 sequence(max, 1),
col6 sequence(count, 1),
col7 sequence(10, 1),
col8 recnum,
col9 sysdate,
col10 date 'YYYY-MM-DD'
)
into table t_test_b
truncate
when flag='2'
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
flag filler position(1),
--注意此处要写position(1),不然sqlldr会继续往后映射,即此处flag会去找第6个字段,
--如果想把一行数据拆成两部分入到两个表,则数据文件样本2,1到5个字段入第1个表,6到10个字段入到第2张表则可不写position(1),
--position(1)相当于把指针移到最前列,然后继续映射。
col1 constant 'z',
col2 nullif col2='2',
col3,
col4 defaultif col3='3',
col5 sequence(max, 1),
col6 sequence(count, 1),
col7 sequence(10, 1),
col8 recnum,
col9 sysdate,
col10 date 'YYYY-MM-DD'
)
表结构:
create table t_test_a
(
col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20),
col5 varchar2(20),
col6 varchar2(20),
col7 varchar2(20),
col8 varchar2(20),
col9 date,
col10 date
);
create table t_test_b
(
col1 varchar2(20),
col2 varchar2(20),
col3 varchar2(20),
col4 varchar2(20),
col5 varchar2(20),
col6 varchar2(20),
col7 varchar2(20),
col8 varchar2(20),
col9 date,
col10 date
);
数据文件样本1
test.csv
1,1,a,1,2011-01-01
1,2,a,2,2011-01-02
1,3,a,3,2011-01-03
1,4,a,4,2011-01-04
1,5,a,5,2011-01-05
2,1,a,1,2011-01-01
2,2,a,2,2011-01-02
2,3,a,3,2011-01-03
2,4,a,4,2011-01-04
2,5,a,5,2011-01-05
数据文件样本2
test.csv
1,1,a,1,2011-01-01,2,1,a,1,2011-01-01
1,2,a,2,2011-01-02,2,2,a,2,2011-01-02
1,3,a,3,2011-01-03,2,3,a,3,2011-01-03
1,4,a,4,2011-01-04,2,4,a,4,2011-01-04
1,5,a,5,2011-01-05,2,5,a,5,2011-01-05
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7417681/viewspace-709803/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7417681/viewspace-709803/