• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Regarding Bulk insert

 
Deepthi Xavy
Ranch Hand
Posts: 32
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hai..

Can anyone just help me out for doing bulk insert in mysql ,I got 60 records for marking attendance for a particular class,all the records has to be inserted in database at a time without using looping concept.I do know how to proceed with query.

I just think of a concept that if the check box were marked,that particular student is absent else the student is present.How to do it with query.Can anyone make me clear with query
 
Swastik Dey
Rancher
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
batch updates might help you

http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html
 
Deepthi Xavy
Ranch Hand
Posts: 32
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya..batch update i tried,but how to proceed with the concept i mean here..
 
Swastik Dey
Rancher
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As per me it does not seem to be possible without a loop, because even with batch update you need to write 60 addBatch statements. Secondly as you have said marking present/absent status, so it seems that you have the student records in table and probably what you need is an update query rather than an insert query.
 
James Ward
Ranch Hand
Posts: 263
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well you could potentially do it with Two Queries instead of looping over a query 60 times:

//Update for Students who are present. The in part of the query you need to construct dynamically.
UPDATE student_table SET present='true' where student_id in ('10041', '10043', '10082', '10091');

//Update for Students who are present. The in part of the query you need to construct dynamically.
UPDATE student_table SET present='false' where student_id in ('10033', '10013', '10088', '10099', '10887');

 
Swastik Dey
Rancher
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not very sure, but might lead to some optimization issues. Please correct me if I am going wrong.
 
James Ward
Ranch Hand
Posts: 263
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:Not very sure, but might lead to some optimization issues. Please correct me if I am going wrong.


Looping is not required for this requirement, and will be much slower.

An index on student_id column is expected, and most likely it will exist if it is a primary key.
The index is expected anyway irrespective of any kind of update.
 
Swastik Dey
Rancher
Posts: 1601
5
Android Java Java ME
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am suggesting to use the loop only for the addBatch statement, so in this case executeBatch is triggered only once. May be the logic can go something like this.

A Hashtable/hashmap contains the roll and the status(true/false)

Start a loop to enumerate through the map


 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you wish to avoid something as elementary as loops?

60 is a small number when it comes to databases. I wouldn't spend much time trying to optimize this for performance, since the difference will most likely be too small to be relevant during runtime.
 
Deepthi Xavy
Ranch Hand
Posts: 32
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
loop might take more time to get executed,because if we want to insert 60 records.the loop has to be executed 60 times..it might be time consuming..so am planing to insert all the records at a time..using bulk insert..at a moment all records has to be inserted.
 
Ulf Dittmer
Rancher
Pie
Posts: 42966
73
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The beauty of batch operations is that they don't get acted upon until all the parts of a batch have been submitted. Whether the client code did that in a loop or not is irrelevant for performance.

As I said, I wouldn't worry too much about performance until you have proven that you need to do do. Preconceived notions of what should or should not perform well often turn out to be incorrect.
 
Deepthi Xavy
Ranch Hand
Posts: 32
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for everyone who helped me out to come from the trouble,let me try out everything...& report
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic