Chinaunix首页 | 论坛 | 博客
  • 博客访问: 103067434
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类: Oracle

2008-03-31 22:44:17

来源:赛迪网    作者:103819

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

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

阅读(582) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~