数据文件:D:\oracletest\ldr_tab_fiile.dat
1、数据文件字段中间以制表符TAB隔开:
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONE SMANAGER
===控件文件:
LOAD DATA
INFILE 'D:\oracletest\ldr_tab_fiile.dat'
REPLACE
INTO TABLE EMP2
FIELDS TERMINATED BY x'' --以制表符tab分隔
(EMPNO,ENAME,JOB)
===查询结果,导入成功
SQL> select * from emp2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONE SMANAGER
2、数据文件字段中间以制表符TAB或空格隔开
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONE SMANAGER
===控制文件
LOAD DATA
INFILE 'D:\oracletest\ldr_tab_fiile.dat'
REPLACE
INTO TABLE EMP2
fields terminated by whitespace --结尾以空白分隔
(EMPNO,ENAME,JOB)
3、使用position做分隔数据值
数据文件:
7369 CH.SMITH CLERK
7499 USA.ALLEN SALESMAN
7521 JP.WARD SALESMAN
7566 HO.JONE SMANAGER
控制文件:
LOAD DATA
INFILE 'D:\oracletest\ldr_tab_fiile.dat'
REPLACE
INTO TABLE EMP2
(
EMPNO position(1:4),
ENAME position(*+1:14), --*表示从上一字段结尾
JOB position(*:25)
)