Inserting multiple record into a database

[Claude Nichols]

hello ranchers,

I got a problem.
I have a servlet that uses the JDBC facilities to connect to a database. The servlet recieves several records at any one time and must access the database and insert the new records into the database. Each record contains info for 6 fields.
I am able to insert a single record at a time but i need to insert multiple records to releave servlet overhead.
Is there a way to achieve multiple record insertion without coding a insert statements for each record or creating a large insert statement encompassing all records.

Thanks in advance

[Daniel Dunleavy]

I have seen a number of postings about "batching inserts", so I would do some searches on this forum with "batch" or "batching"
Dan

[Thomas Suer]

I think the only way is to create a 'large' insert statement of the following form:

INSERT INTO table_name (col1, col2, col3) 
VALUES (data11, data12, data13), (data21, data22, data23), (data31, data32, data33) ...

Tom

[Claude Nichols]

Thanks for you responces
I have heard of batch inserts but never come across it before. If you find anything on how to send me the link and how it works.
I'll however try to use the format for a large insert statement given and see how that goes.

later then

thanks

[Jamie Robertson]

http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html
Jamie
...by the way, I've never heard of large insert statements. I don't think they are standard SQL because it doesn't work with Oracle. I would stick to Batches because they are very efficient and are a portable(standard) solution.

[Claude Nichols]

Unfortunetly I have to use an existing access database and it does not support batch processing.
Throws a UnsupportedOperationException
The suggestion of a large string is still on the table the only problem is that insert values are determined dynamicaly and amount are not previously known.
I'll keep trying
thanks

[Jamie Robertson]

Just a note:
It's not the database that doesn't support batch statements, it's the JDBC driver that doesn't implement it.
another note:
I can use batch statements with the jdbc dbc bridge to an access database, which is as bare bones as it can get. Unless you are using some obscure database, there is a driver that will support batch updates.
Which database/driver/jdk combination are you using? may be you are using an old jdbc driver or an older jdk
Jamie