sqlldr一行記錄拆分為多行

redhouser發表於2014-11-03

需求:
將以下檔案中每行資料,按列拆分為多行。如將檔案a.txt插入表中,
檔案a.txt內容:
a 1 2 3
b 11 12 13
c 21 22 23

插入後的效果:
a col1 1
b col1 11
c col1 21
a col2 2
b col2 12
c col2 22
a col3 3
b col3 13
c col3 23


--目標表
SQL> desc tmp_mh
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 X          VARCHAR2(10)
 KEY          VARCHAR2(10)
 VALUE          NUMBER(38)


--控制檔案:tmp.ctl
--只有使用position,才會從行首讀取資料
LOAD DATA
INTO TABLE tmp_mh
( x POSITION(1:1),
  key constant "col1",
  value TERMINATED BY WHITESPACE,
  x3 filler TERMINATED BY WHITESPACE,
  x4 filler TERMINATED BY WHITESPACE
)
INTO TABLE tmp_mh
( x POSITION(1:1),
  key constant "col2",
  x2 filler TERMINATED BY WHITESPACE,
  value TERMINATED BY WHITESPACE,
  x4 filler TERMINATED BY WHITESPACE
)
INTO TABLE tmp_mh
( x POSITION(1:1),
  key constant "col3",
  x2 filler TERMINATED BY WHITESPACE,
  x3 filler TERMINATED BY WHITESPACE,
  value TERMINATED BY WHITESPACE
)


sqlldr userid/userid control=tmp.ctl data=a.txt log=a.log

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18922393/viewspace-1318526/,如需轉載,請註明出處,否則將追究法律責任。

相關文章