數據批量導入orale資料庫
最近的一個實驗，覺得還行，放上來看看
http://dancfan.blogbus.com/c3014784/這個裡面的文檔不錯，比較適合新人和初學者，99%為原創，有興趣的看看
環境：windows 2003 oracle10.1.0.2.0
實驗一
C:\Documents and Settings\Administrator>sqlplus fygl/fygl123@fygl
SQL*Plus: Release 10.1.0.2.0 - Production on 星期六 12月 19 23:23:04 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
連接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table weather(
2 outlook varchar(20),
3 temperature float,
4 humidity float,
5 windy varchar(10),
6 play varchar(10)
7 );
表已創建。
在E盤建立如下兩個文件result.csv，result.ctl
result.ctl
load data
infile 'result.csv'
into table weather
(outlook char terminated by ',',
temperature char terminated by ',',
humidity char terminated by ',',
windy char terminated by ',',
play char terminated by ','
)
result.csv
sunny,85,85,FALSE,no
sunny,80,90,TRUE,no
overcast,83,86,FALSE,yes
rainy,70,96,FALSE,yes
rainy,68,80,FLASE,yes
rainy,65,70,TRUE,no
overcast,64,65,TRUE,yes
sunny,72,95,FALSE,no
sunny,69,70,FALSE,yes
rainy,75,80,FALSE,yes
sunny,75,70,TRUE,yes
overcast,72,90,TRUE,yes
overcast,81,75,FALSE,yes
rainy,71,91,TRUE,no
E:\>sqlldr userid=fygl/fygl123 control=result.ctl
SQL*Loader: Release 10.1.0.2.0 - Production on 星期三 12月 23 10:38:36 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
達到提交點 - 邏輯記錄計數 13
達到提交點 - 邏輯記錄計數 14
E:\>
SQL> select * from weather;
OUTLOOK TEMPERATURE HUMIDITY WINDY PLAY
-------------------- ----------- ---------- ---------- ----------
sunny 85 85 FALSE no
sunny 80 90 TRUE no
overcast 83 86 FALSE yes
rainy 70 96 FALSE yes
rainy 68 80 FLASE yes
rainy 65 70 TRUE no
overcast 64 65 TRUE yes
sunny 72 95 FALSE no
sunny 69 70 FALSE yes
rainy 75 80 FALSE yes
sunny 75 70 TRUE yes
OUTLOOK TEMPERATURE HUMIDITY WINDY PLAY
-------------------- ----------- ---------- ---------- ----------
overcast 72 90 TRUE yes
overcast 81 75 FALSE yes
rainy 71 91 TRUE no
實驗二
在E盤建立如下兩個文件result.csv，result.ctl
result.csv
1,默認WEB站點,192.168.2.254:80:,RUNNING
2,other,192.168.2.254:80:test.com,STOPPED
3,third,192.168.2.254:81:thirdabc.com,RUNNING
result.ctl
load data
infile 'result.csv'
into table resultest
(resultid char terminated by ',',
website char terminated by ',',
ipport char terminated by ',',
status char terminated by whitespace)
E:\>sqlplus fygl/fygl123@fygl
SQL*Plus: Release 10.1.0.2.0 - Production on 星期三 12月 23 11:02:19 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
連接到:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table resultest
2 (resultid varchar2(500),
3 website varchar2(500),
4 ipport
《解決方案》
原帖由 atianyu 於 2009-12-25 22:58 發表 http://linux.chinaunix.net/bbs/images/common/back.gif
最近的一個實驗，覺得還行，放上來看看
http://dancfan.blogbus.com/c3014784/這個裡面的文檔不錯，比較適合新人和初學者，99%為原創，有興趣的看看
環境：windows 2003 oracle10.1.0.2.0
實驗一
C: ...
:em10: :em10: :em10: 幫頂
《解決方案》
回復 #2 blueswxs 的帖子
謝謝了哥們
《解決方案》
ctl里不用每行都寫terminated by ','
fields terminated by ','就行了
