一个急手的sql loader问题，请大家帮忙，谢谢！

Harry_shan

最初由 tina_xu 发布
[B]将control文件中每个table的前两个栏位加了个position指向就过了 [/B]

ziruobing

最初由 tina_xu 发布
[B]将control文件中每个table的前两个栏位加了个position指向就过了 [/B]

为什么啊?
必要每个字段都加上position指向吗?

ziruobing

最初由 ziruobing 发布
[B]


为什么啊?
必要每个字段都加上position指向吗? [/B]

如果数据的长度不固定呢~
那该怎么处理呢?

huangkungan

最初由 ziruobing 发布
[B]


如果数据的长度不固定呢~
那该怎么处理呢? [/B]

可以用 TERMINATED BY 指定分隔符。
如',' 或tab字符

huajian15

楼上的正解

ziruobing

最初由 huangkungan 发布
[B]

可以用 TERMINATED BY 指定分隔符。
如',' 或tab字符 [/B]

load data
infile *
DISCARDFILE 'C:/SqlLoader/test.dsc'
replace
into table TEST
when TESTID != "testId1"
fields terminated by ','
optionally enclosed by '"'
(TESTID, TESTNAME,TESTAGE)

into table TEST2
when TESTID = "testId4"
fields terminated by ','
optionally enclosed by '"'
(TESTID, TESTNAME,TESTAGE)
begindata
"testId1","testName1","testAge1","testAge11"
"testId2","testName2","testAge2","testAge22"
"testId3","testName3","testAge3","testAge33"
"testId4","testName4","testAge4","testAge44"

可是我这样写的话,
两个when条件只能执行第一个when条件呢~
就像上面的只能执行TEST表
如果把上面的TEST和TEST2位置换一下,那么就只会执行TEST2
(TEST和TEST2是结构完全相同的两张表)
但是按下面这种指定POSITION的写法就不会有问题~

load data
infile *
DISCARDFILE 'C:/SqlLoader/test.dsc'
replace
into table TEST
when TESTID != "testId1"
(TESTID POSITION(2:8),
TESTNAME POSITION(12:20),
TESTAGE POSITION(24:31),
field4 FILLER POSITION(35:43))

into table TEST2
when TESTID = "testId4"
(TESTID POSITION(2:8),
TESTNAME POSITION(12:20),
TESTAGE POSITION(24:31),
field4 FILLER POSITION(35:43))
begindata
"testId1","testName1","testAge1","testAge11"
"testId2","testName2","testAge2","testAge22"
"testId3","testName3","testAge3","testAge33"
"testId4","testName4","testAge4","testAge44"

ziruobing

UP