| |
实例解析：sqlldr加载数据到不同表的问题
◆首先我们来创建测试表：
d:\orion>sqlplus eygle/eygle
sql*plus: release 9.2.0.6.0 - production on 星期一 11月 11 12::20 2007
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
连接到:
oracle9i enterprise edition release 9.2.0.6.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.6.0 - production
19:53:59 sql> create table test1 (
19:54:14 2 a1 varchar2(10),
19:54:21 3 a2 varchar2(10),
19:54:26 4 a3 varchar2(10));
表已创建。
已用时间: 00: 00: 00.03
19:54:32 sql> create table test2 (
19:54:35 2 a1 varchar2(10),
19:54:39 3 a2 varchar2(10),
19:54:40 4 a3 varchar2(10));
表已创建。
已用时间: 00: 00: 00.04
23:21:42 sql> exit
从oracle9i enterprise edition release 9.2.0.6.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.6.0 - production中断开
|
◆然后我们来测试数据:
d:\orion>cat data.txt
01,kunming,yunnan
02,beijing,beijing
02,shenzhe,shenzhe
02,tianjin,tianjin
d:\orion>
|
◆控制文件
d:\orion>cat data.ctl
load data
infile 'data.txt'
append into table test1
when (2) = '1'
fields terminated by "," optionally enclosed by " " trailing nullcols
( a1 position(01:02), a2 position(04:10), a3 position(12:19) )
into table test2
when (2) = '2'
fields terminated by "," optionally enclosed by " " trailing nullcols
( a1 position(01:02), a2 position(04:10), a3 position(12:19) )
|
◆加载数据
d:\orion>sqlldr eygle/eygle errors=20000 log=data.log control=data.ctl
sql*loader: release 9.2.0.6.0 - production on 星期一 11月 11 12:23:21 2007
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
达到提交点，逻辑记录计数3
达到提交点，逻辑记录计数4
|
◆检查结果
d:\orion>sqlplus eygle/eygle
sql*plus: release 9.2.0.6.0 - production on 星期一 11月11 12:31:29 2007
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
连接到:
oracle9i enterprise edition release 9.2.0.6.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.6.0 - production
19:57:38 sql> select * from test1;
a1 a2 a3
---------- ---------- ----------
01 kunming yunnan
已用时间: 00: 00: 00.00
19:57:42 sql> select * from test2;
a1 a2 a3
---------- ---------- ----------
02 beijing beijing
02 shenzhe shenzhe
02 tianjin tianjin
已用时间: 00: 00: 00.00
19:57:45 sql> exit
从oracle9i enterprise edition release 9.2.0.6.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.6.0 - production中断开
|
注释：假如你不选择分区表，就可以用这个方式来直接加载数据到不同的数据表中。
|
|
