java 1.4 -sql server2000:is it safe to append text to String in java of size in gbs.

Question

i am dynamically creating sql query for inserting multiple records in one hit.Suppose no of record to be inserted is 1000.in that case sql query will be generated as below in code

  String insertBatchSqlQuery="";

  for(int currentRecordIndex=0;currentRecordIndex<totalNoOfRecords;currentRecordIndex++)
  {
   insertBatchSqlQuery+=getSQL("InsertFileUploadData");//note SQL query retrieved from SQL repository must end with semi colon
   for(int currentFieldIndex=0;currentFieldIndex<countOfProperties;currentFieldIndex++)
   {
    insertBatchSqlQuery+=getSQL("InsertFileUploadRecordFieldData");
   }
  }

In this case in case no of record is 1000 and noOfproperties is 80.then 80000 insert statements will be appended to String variable.Is it safe to use String in this case.Is there any issue while executing this query.

after implementing executebatch as suggested by my frnd below i got exception com.microsoft.jdbc.base.BaseBatchUpdateException on execute update with message"operation was cancelled at user request".batch contains 500 queries approximately.what can be cause?

Answer 1

You should have a look at batch updates instead of joining everything to one big string.

Answer 2

Agreed with Tangens, I would never run 500 individual update statements like that. Maybe you can consider building a query like this, instead of having each INSERT on its own:

INSERT dbo.table(column1, column2)
SELECT           'foo1', 'bar1'
UNION ALL SELECT 'foo2', 'bar2'
UNION ALL SELECT 'foo3', 'bar3'
UNION ALL ...