sqlldr加载数据到不同表的问题

实例讲解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中断开

注释：假如你不选择分区表，就可以用这个方式来直接加载数据到不同的数据表中。

（出处：清风网络学院）