Bulk inserting to mysql DB table using perl

Question

I am using a simple perl script to pupulate millions of rows in a mysql DB table. I'm using perl DBI and DBD::mysql with the script. Example code below

my $dbh = DBI->connect(<DB INFO>);
my $sth;
my $insert_com = "INSERT INTO TAB1 VALUES(?,?,?,?,?)";
for (1..50000000){

   $sth = $dbh->prepare($insert_com);
   $sth->execute(<val1>,<val2>,<val3>,<val4>,<val5>);

}

As per the above code, I think a commit is sent for each iteration of the loop. The question I have is, whether it's possible to send a commit every n iteration ? i.e to commit after inserting n number of rows to the table. If its possible, can someone please tell me how. Thanks in advance. Cheers ...

possible duplicate of [MySQL Insert 20K rows in single insert](http://stackoverflow.com/questions/22164070/mysql-insert-20k-rows-in-single-insert) — Tony Hopkinson, Sep 10 '14 at 09:09
Take ` $sth = $dbh->prepare($insert_com);` statement out of loop — Eugen Konkov, May 11 '16 at 14:01

score 3 · Accepted Answer · answered Sep 10 '14 at 09:07

3

You have to set then "AutoCommit to zero:

$dbh = DBI->connect($dsn, $user, $password,
                      { RaiseError => 1, AutoCommit => 0 });

and call all n rows $dbh->commit()

See DBI Documentation for more details.

answered Sep 10 '14 at 09:07

Jens

67,715
15
98
113

Bulk inserting to mysql DB table using perl

1 Answers1