Multiple files through SQL Loader-CodePudding

I have two requirements of loading data in Oracle Tables through SQL Loader utility -

Requirement 1

Two .csv files with same headers defined in both the files. Skip the header from both and load the combined data in the table.

What will be the command to load the data by skipping the headers and stop the process if either of files have errors.

Requirement 2

Two files with attributes spread across in both the files i.e.,
Table Primary key - ID,Name
Cols in First file - ID,Name,Attr1
Cols in Second File - ID,Name,Attr2

Columns in the oracle table where the both the files' data will be loaded
ID,Name,Attr1,Attr2

What will be the best way to load the attributes from both the files in this case? How to handle data integrity scenarios ? i.e., notify or do not load attributes from 2nd file if 1st file corresponding records are bad records.

Thanks in Advance.

CodePudding user response：

The way you described it, SQL*Loader is not the tool of your choice. External tables, no the other hand, might be.

Why? Because SQL*Loader will load the 2nd file regardless of errors found in the 1st file. Also, you can't load from two files and "merge" data into a single record in the target table. (OK, its background still is SQL Loader, but that's not the point here).

However, if each of those CSV files represents an external table, then you can access them using SQL or - maybe even better - PL/SQL. As it is a procedural extension to SQL, you'd create a procedure which "loads" (that would be INSERT) the 1st file's contents into the target table. You'll be able to check whether there were any errors and then proceed to the 2nd file, using either UPDATE or MERGE to set the attr2 column's value.

CodePudding user response：

Order the files by id,name:

cat file1.txt | sort > file1.csv
cat file2.txt | sort > file2.csv

If you need to discrd the header use grep -v:

cat file1.txt | grep -v "header id.." | sort > file1.csv
cat file2.txt | grep -v "header id.." | sort > file2.csv

then merge the files using awk:

awk 'BEGIN {FS=","}{getline line < "file1.csv"; print line","$3}' file2.csv > inputSqlLoader.csv

the load the resulting file with sql loader. Use the skip=1 option on sql loader to discard the header, if needed.