Batch Insert

[Arulanand Dayalan]

Hi all,

I am using oracle database. I want to do a batch insert, i want to know is it possible. I have Distribution Class which contains Distribution Package Id, Product Type List and Channel List. I have around List of 50000 Distribution objects which needs to be inserted into DB.

I have two table Product , Product Channel.

Product - Product Id (Sequence,PK),Package Id, Product Type
Product Channel - Proudct Id , Channel Type

I want to do the insert as batch so that i can improve performance. My algorithum would look like thsi,

for each Distribution Object in the List
Get the Package Id
Get the Product List
Get the Channel List
for each Product Type in the Proudct List
insert in to DB - Product Type,Package Id. Get the Product Id
for each Channle in the Channel List
insert in to DB - Product Id,Channel Type
end
end
end

It would be great if some could tell how to do it or guide me to some resources where i could get more information.

Thanks in Advance.

Regards,
Arul.

[Stevi Deter]

The basic format for creating and executing a batch in JDBC 2 is:

try {
  connection.setAutoCommit(false);
 
  Statement stmt = connection.createStatement();
  stmt.addBatch("INSERT INTO Product (PackageId, ProductType) "
      + "VALUES ( 1, 'type')");
  stmt.addBatch("INSERT INTO Product  " + "VALUES (2, 'type')");
 
  int[] updCnt = stmt.executeBatch();
  connection.commit();
 
} catch (BatchUpdateException be) {
 
  // handle batch update exception
  int[] counts = be.getUpdateCounts();
  for (int i = 0; i < counts.length; i++) {
    System.out.println("Statement[" + i + "] :" + counts[i]);
  }
  connection.rollback();
} catch (SQLException e) {
  // handle SQL exception
  connection.rollback();
}

However, given the complexity of your specific code, you may want to look at creating a stored procedure and calling it, as that will make it easier to leverage getting the ID back from the first insert, etc.

You could also look into a batch insert using a select subquery ("insert into TABLE () values select ...")

[Arulanand Dayalan]

Thanks Stevi Deter for quick reply. I am planning to do this using Stored Procedure. Think this way it would perform better.

Regards,
Arul.