« 如何从备份集中恢复归档日志 | Blog首页 | 王小慧作品 »
如何使用sqlldr的WHEN判断加载数据到不同的数据表
作者：eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：https://www.eygle.com/archives/2005/07/ecieoasqlldraew.html
今天有朋友问到sqlldr加载数据到不同表的问题，做了一个测试，示例如下:链接：https://www.eygle.com/archives/2005/07/ecieoasqlldraew.html
创建测试表
D:\Orion>sqlplus eygle/eygle SQL*Plus: Release 9.2.0.6.0 - Production on 星期日 7月 3 19:53:59 2005 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 19:54: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 星期日 7月 3 19:57:36 2005 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 星期日 7月 3 19:57:38 2005 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中断开 |
如果不选择分区表，可以使用此方式直接加载数据到不同的数据表中。
.
历史上的今天...
>> 2017-07-03文章:
>> 2011-07-03文章:
>> 2008-07-03文章:
>> 2007-07-03文章:
>> 2006-07-03文章:
>> 2004-07-03文章:
历史上的今天...
>> 2017-07-03文章:
>> 2011-07-03文章:
>> 2008-07-03文章:
>> 2007-07-03文章:
>> 2006-07-03文章:
>> 2004-07-03文章:
By eygle on 2005-07-03 19:33 | Comments (1) | Backup&Recovery | 328 |
要是数据文件长短不一
01,KunMing,YunNan
02,BeiJisdng,BeiJing
02,ShenZdhe,ShenZhe
02,TianfffJin,TianJin
就不行了！