CODEWITHSUNDEEP
mysqlperl
Kamrul Khan
Kamrul Khan

Reputation: 3350

Perl DBI how to prepare single insert multiple rows

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 ?

Upvotes: 2

Views: 6420

Answers (2)

Q the Platypus
Q the Platypus

Reputation: 845

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.

Upvotes: 2

friedo
friedo

Reputation: 66937

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 );
}

Upvotes: 0

Related Questions