0

I have 1000 load files and I know we can load multiple files into one table like this:

LOAD DATA
  INFILE file1.txt
  INFILE file2.txt

  APPEND
  INTO TABLE emp
  ( empno  POSITION(1:4)   INTEGER EXTERNAL,
    ename  POSITION(6:15)  CHAR,
    deptno POSITION(17:18) CHAR,
    mgr    POSITION(20:23) INTEGER EXTERNAL
  )

But my problem is : I want to insert the data of file1.txt into emp table with some constant values "Cons1" and with some different constant value "Cons2" for the file file2.txt.

I am really stuck into this and tried so much on the net.any help really really appreciated 

 LOAD DATA
  INFILE file1.txt 
  APPEND
  INTO TABLE emp
  ( empno  POSITION(1:4)   INTEGER EXTERNAL,
    ename  POSITION(6:15)  CHAR,
    deptno POSITION(17:18) CHAR,
    mgr    POSITION(20:23) INTEGER EXTERNAL,
    **ConstColumn constant "Cons1"**
  )
 INFILE file2.txt  
  APPEND
  INTO TABLE emp
  ( empno  POSITION(1:4)   INTEGER EXTERNAL,
    ename  POSITION(6:15)  CHAR,
    deptno POSITION(17:18) CHAR,
    mgr    POSITION(20:23) INTEGER EXTERNAL,
    **ConstColumn constant "Cons2"**
  )
meijuh
  • 1,067
  • 1
  • 9
  • 23
Ankur Bhutani
  • 3,079
  • 4
  • 29
  • 26

1 Answers1

0

SQL*Loader just takes input from all files as if they were one file, still treating all rows as if they came from one file. Each file would have to have an identifying column that indicates which file it came from and then you would key off of that using multiple WHEN clauses. With 1000 files that task seems daunting if the files are indeed the same and don't already have that structure. Maintaining that control file would be a nightmare anyway.

You could create a skeleton control file with placeholders for the constant, then write a wrapper program that would loop through the files in the folder and for each one copy the skeleton and replace the placeholder with the constant text you want, then call SQL*Loader, using the command-line argument to pass the filename and use that control file.

Let us know how you end up solving this.

Gary_W
  • 9,933
  • 1
  • 22
  • 40
  • Gary,Can you please add some reference links for placeholders for the constant and wrapper? – Ankur Bhutani May 28 '15 at 09:12
  • See this previous post where I explain it further: https://stackoverflow.com/questions/27250002/insert-timestanp-of-infile-into-a-column-from-sqlloader/27280827#27280827 – Gary_W May 28 '15 at 12:52