Issue with Batch Insertion

[sammaiah kyatham]

Hi All,
can some one please through a best approach to go ahead for below problem:

We have a requirement to insert huge number of records in batches.
In case if one record fails, the application should continue with remaining records with out rollback remaining records.

We are using commit only end of all the records and even not able to trace which record get failed in batch approach.

Thnx,
sam

[Martijn Verburg]

If you are not requiring rollback, then maybe you can look at _not_ wrapping the batch job in a transaction.

Hope that helps!

[David O'Meara]

I would still consider batches within a transaction, but cut into smaller batches so that you can manage each batch as a transaction so that failures can be managed individually

public void doAll()
{
  for each group of X items
  {
    doBatch(items);
  }
}
public void doBatch(List items)
{
  try
  {
     send everything in a single batch
  }
  catch(SQLException sqle)
  {
    rollback();
    for each item
    {
      try
      {
        insert
      }
      catch(SQLException sqle)
      {
        report failure;
      }
    }
  }
  commit;
}

more or less...
[fixed nested block]
[ September 22, 2008: Message edited by: David O'Meara ]

[David O'Meara]

I'm not sure what X should be, but if you have few bad records then it can be large (a thousand or more), otherwise you'll need to make it smaller or incur the wrath of individual inserts.

[sammaiah kyatham]

Hi,
Thanks for the response.
The data volume is really high, like 6months past history of customers... It could be more than 1 crore records in real environment...

It would be great if any one could through a best approach...

Thnx,
Sam

[David O'Meara]

better get started then