can I use sql loader to update an already exiting table in my oracle 11g database?

GP7
GP7 used Ask the Experts™
on
Hi all,

I need to load data from flat files in to my Oracle tables. Once I load data from the flat files, every day I get a flatfile/table (with the same name). That new flat file contains  just inserts and updates. Now if i use sql loader again, then then the Inserts will be inserted into the tables, but, since the updates will have the same aprimary key as the ones in the oarcle table, sql loader throws an error saying "primary key violation". The fix is to load the new data into an external table first and use the merge clause and load it into the oracle table.

But, my question is .... can I do this directly using merge clause with sql loader?

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
there is no direct merge clause with sqlloader ... as u said u could load it into a staging table or temporary and then use the sql MERGE command.
GP7

Author

Commented:
But, Shivkasi, as I mentioned earlier, I have 100 such tables. So, I'll need to load all the 100 flat files in to 100 external tables and merge the external tables with my already existing Oracle tables? I mean is there a better way to do this? I will have to load and merge on a daily basis.

Ok... even if I create 100 external tables and load the flat files into these tables using SQL loader, how do I kick off or automate the loading and merging process?

i want to know if there is a better way then....
sqllrd .... for file 1
sqlldr .... for file 2
sqlldr .... for file 3
.
.
.
I know this is not the question that I initially asked, but it'll be a great help if you can suggest something.
Commented:
basically u  cannot load data from multiple files into multiple tables with single controlfile. but if is there is  any specific keyword in this file to load in to certain table bcoz if the table structure is the same then u can merge in to a single table and use when clause and load with single control file ...
 if u want to load on daily basis try to create a batch file

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial