各位好,我有个数据文件,每行数据里的前面部分包含公共信息,后面部分为循环5次的信息。比如:
pub_col1, pub_col2, pub_col3, a_col1, a_col2, a_col3, b_col1, b_col2, b_col3,。我需要用sql loader导入这份文件,怎样才能将这样一行记录拆分为5行呢?如下:
pub_col1, pub_col2, pub_col3, a_col1, a_col2,a_col3
pub_col1, pub_col2, pub_col3, b_col1, b_col2,b_col3
pub_col1, pub_col2, pub_col3, c_col1, c_col2,c_col3谢谢!
pub_col1, pub_col2, pub_col3, a_col1, a_col2, a_col3, b_col1, b_col2, b_col3,。我需要用sql loader导入这份文件,怎样才能将这样一行记录拆分为5行呢?如下:
pub_col1, pub_col2, pub_col3, a_col1, a_col2,a_col3
pub_col1, pub_col2, pub_col3, b_col1, b_col2,b_col3
pub_col1, pub_col2, pub_col3, c_col1, c_col2,c_col3谢谢!
select pub_col1, pub_col2, pub_col3, b_col1, b_col2,b_col3 from a union
select pub_col1, pub_col2, pub_col3, c_col1, c_col2,c_col3 from a union
…… ……
第一次 取:
pub_col1, pub_col2, pub_col3, a_col1, a_col2,a_col3
第二次 取:
pub_col1, pub_col2, pub_col3, b_col1, b_col2,b_col3
…… ……
load data
infile '001.txt'
badfile '001.bad'
discardfile '001.dsc'
truncate
into table bcesdata.hdz_test
fields terminated by '|'
TRAILING NULLCOLS
(
pub_col position(1) CHAR,
pub_col2 CHAR,
s_col CHAR,
s_col1 filler CHAR,
s_col2 filler CHAR,
s_col3 filler CHAR
)
into table bcesdata.hdz_test
fields terminated by '|'
TRAILING NULLCOLS
(
pub_col position(1) CHAR,
pub_col2 CHAR,
s_col1 filler CHAR,
s_col CHAR,
s_col2 filler CHAR,
s_col3 filler CHAR
)
into table bcesdata.hdz_test
fields terminated by '|'
TRAILING NULLCOLS
(
pub_col position(1) CHAR,
pub_col2 CHAR,
s_col2 filler CHAR,
s_col1 filler CHAR,
s_col CHAR,
s_col3 filler CHAR
)
into table bcesdata.hdz_test
fields terminated by '|'
TRAILING NULLCOLS
(
pub_col position(1) CHAR,
pub_col2 CHAR,
s_col3 filler CHAR,
s_col1 filler CHAR,
s_col2 filler CHAR,
s_col CHAR
)