Anyone knows which is the best approach to load a large file (about 50000 rows) into database through a web application. The process must check if the record exists and update it, otherwise insert it!
Thanks for your help!! :)
If you are loading to a single table and not to a complex data model, maybe you can try this.
1. Replicate the table T_ACTUAL as T_TEMP (one time DBA job)
2. Do a JDBC batch insert (all 50000 records) to T_TEMP
3. Do a JDBC batch update (all 50000 records) to T_ACUTAL (inserts will simply get ignored with '0 records returned/updated')
4. Execute an insert into T_ACTUAL from T_TEMP for missing records using INSERT INTO T_ACTAUL select * from T_TEMP where NOT EXISTS IN T_ACTUAL
5. Clean up T_TEMP
The above could perform well depending on the performance of indexes on the reqd table. JDBC batch updates should ideally operate with the same speed as sqlldr. 50000 records should be a matter of seconds.
If you are loading to a complex data model, a stored procedure might prove to be a good option.
NOT EXISTS IN T_ACTUAL would actually need a select with a join to the parent. i was being lazy :-)
Don't know if this might be overkill for what you're wanting to do, but you might consider using Hibernate or some intelligent ORM solution. The biggest reason I mention this is that it sounds like you want some intelligence in order to check whether a record already exists in database first. You'd get that functionality with Hibernate directly. It has a method, "saveOrUpdate", which does exactly what you want. For this to work, of course the primary key of the data you're about to insert, much match that of the existing record if there is one in the database already.
Hope that helps...