将txt数据装载到数据库
数据无””,装载定长
LOAD DATA
CHARACTER-SET ZHS16GBK
truncate/append into table a
FIELDS TERMINATED BY ‘,’
TRAILING NULLCOLS
(a position(1:8),
b position(9:38),
c position(39:57)”:c/1000000”
)
a char(8)
b char(30)
c number(18,6)
数据有””，
LOAD DATA
CHARACTER-SET ZHS16GBK
truncate into table a
FIELDS TERMINATED BY ‘分隔符’
OPTIONALLY ENCLOSED BY ‘“‘
TRAILING NULLCOLS
(”a” “trim(:a)”,
”b” “trim(:b)”
)
例如一个字段无分隔符
LOAD DATA
CHARACTER-SET ZHS16GBK
INFILE ‘error.txt’
BADFILE ‘error.bad’
DISCARDFILE ‘error.dsc’
truncate into table a
(acount position(1 :30))
FILEDS给出记录中字段的分隔符。
FILEDS[TERMINATED [BY] (WITHSPACE|’CHARCTER’)]
[OPTIONALLY] ENCLOSED [BY] ‘CHARCTER’]
TERMINATED 读完前一个字段读下一个字段
WITHSPACE 结束符是空格的意思。包括空格，tab,换行符，换页符及回车。
OPTIONALLY ENCLOSED 表示数据由特殊字符括起来。使用OPTIONALLY同时要使用TERMINATED
TRAILING NULLCOLS变表字段没有对应值允许为空
sqlldr 用户名/密码@数据库名 data=/etl/a.txt control=/etl/b.ctl log=/etl/c.log
装载数据如果有重复数据可删除主键
select constraint_name from user_constraints where constraint_type=‘p’
and owner=‘用户名’ and table_name=‘大写表名’;
alter table a drop constraint xxx;
导入数据后增加主键
alter table a add constraint pk_a primary key(xx,xx);
或者
alter table a drop primary key;
数据加载后再建主键
alter table a add primary key(zz,xx);
违反唯一标志时候
alter table a enable row movement;