以下是一个将一份数据文件入到两张表的例子，注意第二张表字段的写法。

控制文件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博客

sqlldr一份数据入两张表

最新文章