实例讲解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) ) |