ä¸é¢è¦è¯´çæ¯å¦æå°txtææ¬æ°æ®å¯¼å
¥å°Oracleä¸
Dos ç¯å¢ä¸ä½¿ç¨SQl*Loaderå½ä»¤ å è½½
使ç¨å
¶å®æ°æ®åºçæ°æ®è½¬ç§»å·¥å
·
Oracle ä¼ä¸ç®¡çå¨ä¸çæ°æ®å è½½åè½
å
·ä½çææ¯å®ç°
ä¸ãDos ç¯å¢ä¸å è½½
1ãé¦å
ï¼æå¡å¨ç«¯ç侦å¬æå¡å¿
须已ç»å¼å¯ã
æµè¯æ¹æ³ï¼Dos ä¸è¾å
¥
C:/>sqlplus username/password@serviceName
2ãç¶åä½¿ç¨ Oracle ç sqlldr å½ä»¤è¿è¡æ°æ®ç导å
¥
åææ¡ä»¶
1ï¼ Oracle æ°æ®åºç«¯å¿
须已ç»å»ºå¥½äºéè¦å¯¼å
¥çæ°æ®è¡¨çç»æ
2ï¼ ä¸ä¸ªæ°æ®æºæ件 ä¸é¢ä¾åä¸ä¸ºå¶è¡¨ç¬¦åéçææ¬æ件 model.txt ,为Excel 表ä¸å¯¼åºç
3ï¼ æå·¥ç¼è¾ä¸ä¸ªXXX.CTL çæ§å¶æ件
4ï¼ å½ä»¤è¡å è½½æ°æ®
å¦ä¸å®ä¾ï¼
以ä¸æ件缺çæ¾å°C:/ ä¸ï¼å¦æä¸æ¯ï¼å°±éè¦ææå
¨è·¯å¾
1ï¼ å½ä»¤æ§å¶æ件 input.ctl å
容
å½ä»¤
说æ
load data
1ãæ§å¶æ件æ è¯
infile 'model.txt'
2ãè¦è¾å
¥çæ°æ®æ件å为test.txt
append into table system.å¡ä½å±æ§è¡¨
3ãå表testä¸è¿½å è®°å½
fields terminated by X'09'
4ãæå®åé符ï¼å段ç»æ¢äºX'09'ï¼æ¯ä¸ä¸ªå¶è¡¨ç¬¦ï¼TABï¼
(ç¼å·,å称,大å°)
5ãå®ä¹å对åºè¡¨ä¸é¡ºåº
æ§å¶æ件ä¸æå®æå
¥æ°æ®çæ¹å¼å
³é®å
insertï¼ä¸ºç¼ºçæ¹å¼ï¼å¨æ°æ®è£
è½½å¼å§æ¶è¦æ±è¡¨ä¸ºç©º
appendï¼å¨è¡¨ä¸è¿½å æ°è®°å½
replaceï¼å é¤æ§è®°å½ï¼æ¿æ¢ææ°è£
è½½çè®°å½
truncateï¼åä¸
å¨ Dos çªå£ä¸ä½¿ç¨ SQl*Loader å½ä»¤å®ç°æ°æ®ç导å
¥
C:/>sqlldr userid=system/manager@ serviceName control=input.ctl
é»è®¤æ¥å¿æ件å为ï¼input.log
é»è®¤åè®°å½æ件为ï¼input.bad
äºã使ç¨å
¶å®æ°æ®åºè½¬ç§»å·¥å
·
以ä¸ä»¥SQL Server 导å
¥å¯¼åºå导为ä¾
1ãå¨æ°æ®ç导å
¥å¯¼åºå导ä¸è®¾ç½®æ°æ®æºæå¡å¨ï¼å®ä¾ä¸éæ©æ°æ®æºéæ©SQL Server
2ãç¶åæå®è¦å¯¼å
¥çOracle æ°æ®æº
3ãéè¦é
ç½®Oracle çå±æ§ä¿¡æ¯
éè¦æ³¨æçæ¯ï¼ç»å½æ°æ®åºçç¨æ·ä¿¡æ¯å³ä¸ºæ°æ®å¯¼å
¥ä¹åçæ¹æ¡åï¼å³å¯¼å
¥ä¹åçSQL Server ä¸ç表å¨Oracle ä¸æ å¿å为 username.表å
以ä¸æç
§æ示å³å¯ï¼å¯ä»¥å®å
¨å¯¼å
¥SQl Server ä¸çæ°æ®è¡¨åè§å¾ï¼ä¹å¯ä»¥ä½¿ç¨æ¥è¯¢è¯å¥è¿åä½ è¦éæ©çåæè
è¡ã
ä¸ãOracle ä¼ä¸ç®¡çå¨ä¸çæ°æ®å è½½åè½
ç»å½Oracle çæ§å¶å°çé¢ï¼é对åç¬çæ°æ®è¡¨å¯ä»¥ä½¿ç¨æ°æ®å 载工å
·
ä¸é´éè¦æå®æ§å¶æ件çï¼åDos å è½½ä¸è´ï¼ä¸åéå¤
----------------------------------------------------
åOracleä¸å¯¼å
¥ææ¬æ°æ®æ¶ä½¿ç¨çæ§å¶æä»¶æ ¼å¼
æ 论æ¯ä½¿ç¨ä¸ä¸ç¯ä¸çåªç§æ¹å¼é½éè¦æä¸ä¸ªæ§å¶æ件ï¼ä¸é¢æ¯æ§å¶æ件ï¼ctlæ件ï¼ä¹¦åçåºæ¬æ ¼å¼ï¼
å½ä»¤
说æ
load data
1ãæ§å¶æ件æ è¯
infile 'testl.txt'ã
2ãè¦è¾å
¥çæ°æ®æ件å为test.txtï¼æ¤æ¶æ¯è¦å¯¼å
¥çæ°æ®æ件åæ§å¶æ件å¨åä¸è·¯å¾ä¸ï¼å¦æä¸å¨åä¸è·¯å¾ä¸åéè¦åå®æ´è·¯å¾å
append into table 表åï¼å¯ä»¥æ¯å
¨åä¹å¯ä»¥æ¯åä¹è¯ï¼
3ãå表testä¸è¿½å è®°å½
fields terminated by X'09'
4ãæå®åé符ï¼å段ç»æ¢äºX'09'ï¼æ¯ä¸ä¸ªå¶è¡¨ç¬¦ï¼TABï¼ï¼å¦æç¨éå·åå²å°±å°X'09'æ¿æ¢ä¸º','
(ç¼å·,å称,大å°)
5ãå®ä¹å对åºè¡¨ä¸é¡ºåº
æ§å¶æ件ä¸æå®æå
¥æ°æ®çæ¹å¼å
³é®å
insertï¼ä¸ºç¼ºçæ¹å¼ï¼å¨æ°æ®è£
è½½å¼å§æ¶è¦æ±è¡¨ä¸ºç©º
appendï¼å¨è¡¨ä¸è¿½å æ°è®°å½
replaceï¼å é¤æ§è®°å½ï¼æ¿æ¢ææ°è£
è½½çè®°å½
truncateï¼åä¸
æ§å¶æ件ç示ä¾ï¼
load data
infile 'test.txt'
append into table test.test
fields terminated by X'09'
(test,test1,test2)
对ææ¶é´ç±»åçæ°æ®å¯¼å
¥ç示ä¾æ§å¶æ件ï¼
load data
infile 'h:/TB_FACT_PHS_TICKET_DAY.txt'
Append into TABLE TB_FACT_PHS_TICKET_DAY
fields terminated by X'09'
(Time_Id
,Region_Id
,Cust_Type_Id
,Prod_Type_Id
,Acct_Item_Type_Id
,Acct_Item_Type_Cd
,Exchange97_Cd
,Latn_Cd
,Call_Duration
,Access_In_Duration
,Income
,In_Date Date "YYYY-MM-DD"
)
æ件导å
¥å½ä»¤ C:/>sqlldr userid=test/test@test control=test.ctl(æ¤æ¶æ§å¶æ件test.ctlåå¨C:/è·¯å¾ä¸)
å¨å½ä»¤æ§å¶ç¬¦ä¸è¿å
¥Oracle C:/>sqlplus username/password@serviceName
oracle导å
¥txtæ°æ®æ件2008å¹´07æ30æ¥ ææä¸ 17:21ætxtæä»¶æ ¼å¼çæ°æ®æ件导å
¥oracleçæ¹æ³æ¯å©ç¨sqlloaderå·¥å
·ã
第ä¸æ¥ï¼æææ¬æ ¼å¼çæ°æ®æ件æ¾å
¥Cçãå¦,test.txt
第äºæ¥ï¼å»ºç«æ§å¶æ件append.ctlãï¼ååå¯ä»¥é便å,æ¾Cçä¸ï¼
append.ctlçå
容å¦ä¸ï¼
ããload dataãããããããããã --1ãæ§å¶æ件æ è¯
ãinfile 'test.txt'ãããããã --2ãè¦è¾å
¥çæ°æ®æ件å为test.txt
ãappend into table CTXSYS.testããã--3ãåCTXSYS表空é´ä¸ç表testä¸è¿½å è®°å½
ãfields terminated by X'09' --4ãå段ç»æ¢äºX'09'ï¼æ¯ä¸ä¸ªå¶è¡¨ç¬¦
(id,username,password,sj)ãã -----å®ä¹å对åºé¡ºåº
å
¶ä¸append为æ°æ®è£
è½½æ¹å¼ï¼è¿æå
¶ä»é项ï¼
aãinsertï¼ä¸ºç¼ºçæ¹å¼ï¼å¨æ°æ®è£
è½½å¼å§æ¶è¦æ±è¡¨ä¸ºç©º
bãappendï¼å¨è¡¨ä¸è¿½å æ°è®°å½
cãreplaceï¼å é¤æ§è®°å½ï¼æ¿æ¢ææ°è£
è½½çè®°å½
dãtruncateï¼åä¸
第ä¸æ¥ï¼å¨å½ä»¤æ示符ä¸è¾å
¥å½ä»¤ã
C:/>sqlldr userid=username/password
control=c:/append.ctl æ°æ®åºä¸ç¨åçç¨æ·ååå¯ç
æè
C:/>sqlldr userid=system/manager@ serviceName control=input.ctl
第äºæ¡å½ä»¤ä¸çsystemæ°æ®åºç¨æ·å
managerå¯ç
@serviceName æ¯Oracleä¸æ¬å°é
ç½®æ件çæå¡å
----------------------------------------------------
ä¸äºç»å¸¸åºç°çé®é¢ï¼
1ãå
³äºæ¥ææ ¼å¼çé®é¢ï¼
ctlåºæ¬åæ³è¯¸å¦ï¼
load data
infile 'C:/TP_LOANCONTRACTSUM.txt'
insert into table TP_LOANCONTRACTSUM
fields terminated by '|!'
(
column01,
column02,
column03,
column04 "to_date(:column04,'''yyyy-mm-dd hh24:mi:ss''')",
column05 "to_date(:column05,'''yyyy-mm-dd hh24:mi:ss''')",
column06,
column07,
column08,
column09,
column10,
column11,
column12 "to_date(:column12,'''yyyy-mm-dd hh24:mi:ss''')",
column13
)
2ãå
³äºé¿å符串é®é¢ï¼CTLé»è®¤æ
åµä¸æ¯256ä½ï¼æè
256ä½å·¦å³ï¼ï¼æ以é¿å符串æ¶ä¼å¨logéæ¥éï¼æ示æè¾å
¥çå¼è¶
è¿æ大é¿åº¦ï¼è§£å³åæ³ï¼å¨ctlæ件éåæå®å¤§å°ï¼æ³¨æ个æ
åµï¼ä¸è½åVARCHARåªè½åCHARï¼å¦åæ¥éï¼è¯¸å¦ï¼
load data
infile 'C:/TP_PLEDGECONTRACTINFO.txt'
insert into table TP_PLEDGECONTRACTINFO
fields terminated by '|!'
(
column01,
column02,
column03,
column04,
column05,
column06,
column07 "to_date(:column07,'''yyyy-mm-dd hh24:mi:ss''')",
column08,
column09,
column10 "to_date(:column10,'''yyyy-mm-dd hh24:mi:ss''')",
column11,
column12,
column13,
column14,
column15 "to_date(:column15,'''yyyy-mm-dd hh24:mi:ss''')",
column16,
column17,
column18 "to_date(:column18,'''yyyy-mm-dd hh24:mi:ss''')",
column19,
column20,
column21,
column22,
column23,
column24,
column25 "to_date(:column25,'''yyyy-mm-dd hh24:mi:ss''')",
column26 CHAR(500),
column27,
column28
)
温馨提示:答案为网友推荐,仅供参考