| Merge two control files [message #554493] |
Mon, 14 May 2012 19:03  |
namitanamburi
Messages: 35
Registered: March 2009
|
Member |
|
|
Hello,
1)Is there a way to load two tables from two input files in one control file?
I have two control files.
LOAD DATA
INFILE 'C:\name.txt'
BADFILE 'C:\name.bad'
DISCARDFILE 'C:\name.dsc'
APPEND
INTO emp_tab
fields terminated by ";"
TRAILING NULLCOLS
( Emp_id ,
name,
age)
LOAD DATA
INFILE 'C:\job.txt'
BADFILE 'C:\job.bad'
DISCARDFILE 'C:\job.dsc'
APPEND
INTO job_tab
fields terminated by ";"
TRAILING NULLCOLS
( job_id ,
title,
salary)
Can i load emp_tab and job_table using one control file and two input files name.txt and job.txt?
2)Is there a way to pass the path as a parameter in the control file?
In the job below, when i execute the sqlldr , can I pass C:/job.txt as an input instead of specifying it in control file?
Thanks
Nammu
|
|
|
|
|
|
| Re: Merge two control files [message #554499 is a reply to message #554493] |
Mon, 14 May 2012 23:53  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Quote:
1)Is there a way to load two tables from two input files in one control file?
I have to wonder why, but yes, it can be done as demonstrated below. However, you have to have something that can be used in a WHEN clause to determine which table the data should be loaded into, unless the data for one table is of different types and lengths that would be rejected for the other table.
-- name.txt:
E;7369;SMITH;31
E;7499;ALLEN;31
E;7521;WARD;31
E;7566;JONES;31
E;7654;MARTIN;30
E;7698;BLAKE;31
E;7782;CLARK;30
E;7788;SCOTT;29
E;7839;KING;30
E;7844;TURNER;30
E;7876;ADAMS;29
E;7900;JAMES;30
E;7902;FORD;30
E;7934;MILLER;30
-- job.txt:
J;20;CLERK;800
J;30;SALESMAN;1600
J;30;SALESMAN;1250
J;20;MANAGER;2975
J;30;SALESMAN;1250
J;30;MANAGER;2850
J;10;MANAGER;2450
J;20;ANALYST;3000
J;10;PRESIDENT;5000
J;30;SALESMAN;1500
J;20;CLERK;1100
J;30;CLERK;950
J;20;ANALYST;3000
J;10;CLERK;1300
-- test.ctl:
LOAD DATA
INFILE 'name.txt' BADFILE 'name.bad' DISCARDFILE 'name.dsc'
INFILE 'job.txt' BADFILE 'job.bad' DISCARDFILE 'job.dsc'
APPEND INTO TABLE emp_tab WHEN whichtable='E'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER, emp_id, name, age)
INTO TABLE job_tab WHEN whichtable='J'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER POSITION(1), job_id, title, salary)
-- tables are empty:
SCOTT@orcl_11gR2> SELECT COUNT(*) FROM emp_tab
2 /
COUNT(*)
----------
0
1 row selected.
SCOTT@orcl_11gR2> SELECT COUNT(*) from job_tab
2 /
COUNT(*)
----------
0
1 row selected.
-- load and results:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT * FROM emp_tab
2 /
EMP_ID NAME AGE
---------- ---------- ----------
7369 SMITH 31
7499 ALLEN 31
7521 WARD 31
7566 JONES 31
7654 MARTIN 30
7698 BLAKE 31
7782 CLARK 30
7788 SCOTT 29
7839 KING 30
7844 TURNER 30
7876 ADAMS 29
7900 JAMES 30
7902 FORD 30
7934 MILLER 30
14 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM job_tab
2 /
JOB_ID TITLE SALARY
---------- --------- ----------
20 CLERK 800
30 SALESMAN 1600
30 SALESMAN 1250
20 MANAGER 2975
30 SALESMAN 1250
30 MANAGER 2850
10 MANAGER 2450
20 ANALYST 3000
10 PRESIDENT 5000
30 SALESMAN 1500
20 CLERK 1100
30 CLERK 950
20 ANALYST 3000
10 CLERK 1300
14 rows selected.
|
|
|
|
| Re: Merge two control files [message #554500 is a reply to message #554493] |
Tue, 15 May 2012 00:03 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Quote:
can I pass C:/job.txt as an input instead of specifying it in control file?
Yes, you could use a control file and two command lines as shown below.
-- control file:
LOAD DATA
APPEND INTO TABLE emp_tab WHEN whichtable='E'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER, emp_id, name, age)
INTO TABLE job_tab WHEN whichtable='J'
FIELDS TERMINATED BY ";" TRAILING NULLCOLS
(whichtable FILLER POSITION(1), job_id, title, salary)
-- command lines:
HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test1.log DATA='name.txt'
HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test2.log DATA='job.txt'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
