SQL> create table tab_b(col1 varchar2(2), col2 number(2));表已创建。SQL> create table tab_a(col1 varchar2(2), col2 number(2));表已创建。SQL> COMMIT;提交完成。

A    1A    2A    3B    3B    2B    1

LOAD DATAINFILE ‘D:\oracletest\test.txt‘DISCARDFILE ‘D:\oracletest\testSqlLdr2.dsc‘REPLACEINTO TABLE TAB_AWHEN COL1="A"(COL1 position(1:1),COL2 position(3:4))INTO TABLE TAB_BWHEN COL1="B"(COL1 position(1:1),COL2 position(3:4))

D:\oracletest>sqlldr scott/tiger@orcl control=D:\oracletest\testSqlLdr2.ctlSQL*Loader: Release 11.2.0.1.0 - Production on 星期日 11月 30 21:27:16 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.达到提交点 - 逻辑记录计数 5达到提交点 - 逻辑记录计数 6

SQL> SELECT * FROM TAB_A;CO COL2-- ----------A 1A 2A 3SQL> SELECT * FROM TAB_B;CO COL2-- ----------B 3B 2B 1

SQL> CREATE TABLE EMP_DEPT10 AS SELECT * FROM EMP WHERE DEPTNO=10 AND 1=2;表已创建。SQL> CREATE TABLE EMP_DEPT20 AS SELECT * FROM EMP WHERE DEPTNO=20 AND 1=2;表已创建。SQL> CREATE TABLE EMP_DEPT30 AS SELECT * FROM EMP WHERE DEPTNO=30 AND 1=2;表已创建。SQL> SELECT * FROM EMP_DEPT10;未选定行SQL> SELECT * FROM EMP_DEPT20;未选定行SQL> SELECT * FROM EMP_DEPT30;未选定行SQL>

--D:\oracletest\test1.txt1 7782 CLARK MANAGER 7839 1981-06-09 2450 0 101 7839 KING PRESIDENT 1111 1981-11-17 5000 0 101 7934 MILLER CLERK 7782 1982-01-23 1300 0 10--D:\oracletest\test2.txt2 7369 SMITH CLERK 7902 1980-12-17 800 0 202 7566 JONES MANAGER 7839 1981-04-02 2975 0 202 7788 SCOTT ANALYST 7566 1987-04-19 3000 0 202 7876 ADAMS CLERK 7788 1987-05-23 1100 0 202 7902 FORD ANALYST 7566 1981-12-03 3000 0 20--D:\oracletest\test3.txt3 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 303 7521 WARD SALESMAN 7698 1981-02-22 1250 500 303 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 303 7698 BLAKE MANAGER 7839 1981-05-01 2850 123 303 7844 TURNER SALESMAN 7698 1981-09-08 1500 0 303 7900 JAMES CLERK 7698 1981-12-03 950 0 30

LOAD DATAINFILE ‘D:\oracletest\test1.txt‘INFILE ‘D:\oracletest\test2.txt‘INFILE ‘D:\oracletest\test3.txt‘BADFILE ‘D:\oracletest\testSqlLdr3.bad‘DISCARDFILE ‘D:\oracletest\testSqlLdr3.dsc‘REPLACEINTO TABLE EMP_DEPT10WHEN FLAG="1" --用flag做标记FIELDS TERMINATED BY WHITESPACE(FLAG FILLER POSITION(1), --一定要指定POSITION(1)不能省略，只有指定POSITION(1)才能决定数据从第一列开始读取，否则会出现空值或数据列错位。EMPNO,ENAME,JOB,MGR,HIREDATE DATE "YYYY-MM-DD" ,SAL,COMM,DEPTNO)INTO TABLE EMP_DEPT20WHEN FLAG="2"FIELDS TERMINATED BY WHITESPACE(FLAG FILLER POSITION(1), EMPNO,ENAME,JOB,MGR,HIREDATE DATE "YYYY-MM-DD",SAL,COMM,DEPTNO)INTO TABLE EMP_DEPT30WHEN FLAG="3"FIELDS TERMINATED BY WHITESPACE(FLAG FILLER POSITION(1), EMPNO,ENAME,JOB,MGR,HIREDATE DATE "YYYY-MM-DD",SAL,COMM,DEPTNO)

D:\oracletest>sqlldr scott/tiger@orcl control=D:\oracletest\testSqlLdr3.ctlSQL*Loader: Release 11.2.0.1.0 - Production on 星期日 11月 30 22:32:49 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.达到提交点 - 逻辑记录计数 2达到提交点 - 逻辑记录计数 3达到提交点 - 逻辑记录计数 7达到提交点 - 逻辑记录计数 8达到提交点 - 逻辑记录计数 13达到提交点 - 逻辑记录计数 14

SQL> select * from emp_dept10;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7782 CLARK MANAGER 7839 1981-06-09 2450 0 107839 KING PRESIDENT 1111 1981-11-17 5000 0 107934 MILLER CLERK 7782 1982-01-23 1300 0 10SQL> select * from emp_dept20;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7369 SMITH CLERK 7902 1980-12-17 800 0 207566 JONES MANAGER 7839 1981-04-02 2975 0 207788 SCOTT ANALYST 7566 1987-04-19 3000 0 207876 ADAMS CLERK 7788 1987-05-23 1100 0 207902 FORD ANALYST 7566 1981-12-03 3000 0 20SQL> select * from emp_dept30;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ---------- ---------- ---------- ----------7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 307521 WARD SALESMAN 7698 1981-02-22 1250 500 307654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 307698 BLAKE MANAGER 7839 1981-05-01 2850 123 307844 TURNER SALESMAN 7698 1981-09-08 1500 0 307900 JAMES CLERK 7698 1981-12-03 950 0 30已选择6行。