|
实例解析：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中断开 数据挖掘论坛
|
注释：假如你不选择分区表，就可以用这个方式来直接加载数据到不同的数据表中。
（责任编辑：卢兆林）
数据挖掘论坛 Create By Any-Extract(WL-AE) 数据挖掘研究院
|
