oracle数据导入sqlldr小例子

 表结构：emp

Sql代码  

1. create table EMP   
2. (   
3.   empno    NUMBER(4) not null,   
4.   ename    VARCHAR2(10),   
5.   job      VARCHAR2(9),   
6.   mgr      NUMBER(4),   
7.   hiredate DATE,   
8.   sal      NUMBER(7,2),   
9.   comm     NUMBER(7,2),   
10.   deptno   NUMBER(2)   
11. )  

create table EMP
(
  empno    NUMBER(4) not null,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
)

控制文件：sqlloader.ctl

Sql代码  

1. load data   
2. infile 'emp.csv'  
3. insert          --APPEND/REPLACE   
4. into table emp   
5. fields terminated by ','  
6. (empno,ename,job,mgr,hiredate "to_date(:hiredate,'YYYY/MM/DD')", sal,comm,deptno)  

load data
infile 'emp.csv'
insert          --APPEND/REPLACE
into table emp
fields terminated by ','
(empno,ename,job,mgr,hiredate "to_date(:hiredate,'YYYY/MM/DD')", sal,comm,deptno)

数据文件：emp.csv

Sql代码  

1. 7369,SMITH,CLERK,7902,1980/12/17,800,,20   
2. 7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30   
3. 7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30   
4. 7566,JONES,MANAGER,7839,1981/4/2,2975,,20   
5. 7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30   
6. 7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30   
7. 7782,CLARK,MANAGER,7839,1981/6/9,2450,,10   
8. 7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20   
9. 7839,KING,PRESIDENT,,1981/11/17,5000,,10   
10. 7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30   
11. 7876,ADAMS,CLERK,7788,1987/5/23,1100,,20   
12. 7900,JAMES,CLERK,7698,1981/12/3,950,,30   
13. 7902,FORD,ANALYST,7566,1981/12/3,3000,,20   
14. 7934,MILLER,CLERK,7782,1982/1/23,1300,,10  

7369,SMITH,CLERK,7902,1980/12/17,800,,20
7499,ALLEN,SALESMAN,7698,1981/2/20,1600,300,30
7521,WARD,SALESMAN,7698,1981/2/22,1250,500,30
7566,JONES,MANAGER,7839,1981/4/2,2975,,20
7654,MARTIN,SALESMAN,7698,1981/9/28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981/5/1,2850,,30
7782,CLARK,MANAGER,7839,1981/6/9,2450,,10
7788,SCOTT,ANALYST,7566,1987/4/19,3000,,20
7839,KING,PRESIDENT,,1981/11/17,5000,,10
7844,TURNER,SALESMAN,7698,1981/9/8,1500,0,30
7876,ADAMS,CLERK,7788,1987/5/23,1100,,20
7900,JAMES,CLERK,7698,1981/12/3,950,,30
7902,FORD,ANALYST,7566,1981/12/3,3000,,20
7934,MILLER,CLERK,7782,1982/1/23,1300,,10

命令：

Sql代码  

1. sqlldr scott/tiger control=sqlloader log=log