‘Batch Processing’.. The name itself explains that some job is done in a batch (phase wise). Normally when we have requirement of populating data base table from some external source (may be XML or excel sheet), we do loop with the data that to be inserted into specified data base table. If the data is much more (many lakhs), sometimes we get time out error or Jrun error.

At this point all advise process the data batch wise. How to do it ?

Let say I am having all the data that needs to be inserted into my desired table is variables.qry (this may be of query type or structure type or … )

In my case this is of Query type. Now I want to process all the records through batch. I have decided in one batch I will send 1 lakh.

<cfscript>

// set counter
variables.count = 1;

// get how many records needs to be inserted
variables.totalNoOfRecord = variables.qry.recordCount ;

// batch limit
variables.countLimit = 100000;

// number of batch you need to run
variables.noOfBatch = Ceiling(variables.totalNoOfRecord / 100000);

// get service factory object
variables.objFactory = CreateObject("java","coldfusion.server.ServiceFactory");

// get connection object 
variables.objDataCon = variables.objFactory.getDataSourceService().getDatasource("data source name").getConnection(); 

variables.objDataCon.setAutoCommit(false);
(
If a connection is in auto-commit mode, then all its SQL statements will be executed and committed as individual transactions. Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback. By default, new connections are in auto-commit mode.
)

// set sql statement
variables.sql = "INSERT INTO table name(column_name1, column_name2) VALUES(?,?)";
variables.sqlStatement = variables.objDataCon.prepareStatement(variables.sql);

// add records to batch
for(i=1; i<= variables.noOfBatch; i++) {
variables.countLimit = iif((i*variables.countLimit gte variables.totalNoOfRecord), variables.totalNoOfRecord, i*variables.countLimit);

for(j=variables.count; j<=variables.countLimit; j++) {

// clear parameter
variables.sqlStatement.clearParameters();

variables.sqlStatement.setString(1, value from query);
variables.sqlStatement.setString(2, value from query);

variables.sqlStatement.addBatch();

}
variables.updateCounts = variables.sqlStatement.executeBatch();

// clear batch
variables.sqlStatement.clearBatch();

// increse counter
variables.count = variables.countLimit+1;
}

variables.flag=true;

if(variables.flag){
variables.objDataCon.commit();
}
</cfscript>

Note:
http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html to set different type of parameter