Perl DBI how to prepare single insert multiple rows

Question

I am inserting multiple rows in a table with single insert query using the following format:

INSERT INTO $table (field1,field2) VALUES (value1,value2),(values3,values4);

The number of rows varies. Is there a way to use Perl's prepare statement for this kind of queries ?

For example, if I am inserting only one row I can do like the below:

$query = "INSERT INTO $table (field1,field2) VALUES (?,?)";
$sth = $dbh->prepare($query);
$sth->execute('value1','value2');

However, I want to do something like the below:

$values = '(value1,value2),(values3,values4),(values5,values6)';
$query = "INSERT INTO $table (field1,field2) VALUES ?";
$sth = $dbh->prepare($query);
$sth->execute($values);

Is this possible? or any other ways to achieve this ?

possible duplicate of [Perl DBI insert multiple rows using mysql native multiple insert ability](http://stackoverflow.com/questions/8421711/perl-dbi-insert-multiple-rows-using-mysql-native-multiple-insert-ability) — ThisSuitIsBlackNot, Aug 21 '15 at 14:27

Q the Platypus · Answer 1 · 2016-09-14T23:44:34.397

2

You can build up a query that can do what you want. Assuming that your records are in an array like this.

my @records = ( ['value1', 'value2'], ...) ;

Then you can create a query dynamically and execute it.

my $values = join ", ", ("( ?, ? )") x @records;
my $query  = "INSERT INTO $table (field1,field2) VALUES $values";
my $sth    = $dbh->prepare($query);
$sth->execute(map { @$_ } @$records);

Also in your example you are using string interpolation on the table name. Be careful with that as it can lead to database injections.

edited Sep 14 '16 at 23:44

answered Aug 21 '15 at 05:13

Q the Platypus

825
1
7
13

You need `my $values = join ", ", ("( ?, ? )") x @records;` (note - bracket moved) otherwise you won't get the commas between each bracket – rbennett485 Sep 14 '16 at 14:43
@rbennett485 's I've edited the answer taking in your fix. – Q the Platypus Sep 14 '16 at 23:45

score 0 · Answer 2 · answered Aug 21 '15 at 05:02

0

Put each record in its own array, then make an array of those:

my @records = ( [ 'value1', 'value2' ], [ 'value3', 'value4' ], [ 'value5', 'value6' ] );

Then prepare your INSERT statement:

my $query = "INSERT INTO $table (field1,field2) VALUES (?,?)";
my $sth = $dbh->prepare($query);

Then loop over your records and execute your statement handle for each one:

foreach my $rec ( @records ) { 
    $sth->execute( @$rec );
}

answered Aug 21 '15 at 05:02

friedo

65,762
16
114
184

Thanks for your answer, we insert around 20000 rows per load. We found its much faster if we insert them all within one single insert query. Will it be as fast this way ? – Kamrul Khan Aug 21 '15 at 05:13
3

If you want it to be fast, skip Perl and use `LOAD DATA INFILE`. – friedo Aug 21 '15 at 05:14
3

A lot of that slowness could be caused by DBI committing each insertion individually. Try turning AutoCommit off before starting your loop. – Dave Cross Aug 21 '15 at 09:33

Perl DBI how to prepare single insert multiple rows

2 Answers2