oracle question

[sandor]

I have a java application that connects to an oracle database to perform crud operations. Now I have to add another feature where the user can perform batch adding of records. A file following a certain format is uploaded and the application parses the file and adds each of them to the table in the database.

The additional feature is that before adding, I must go through the whole file and mark all records that are already existing in the database. I could just do a for loop and perform a select statement for every record in the file, but is there a more efficient way to do this?

[pegitha]

Well, my recommendation would be to create a staging table, insert the records into it and then depending on how quickly you needed them to get into the real table you could do one of many things.
Two that come to mind are
1. kick off a store procedure that migrates them over to the other table.
2. have a cron job that runs every so often and moves them over.
Both can check for duplicates.
You might want to think about some constraints, but they do slow the inserts down.