Re: Efficient select/insert

[Jonathan Mangin]

----- Original Message ----- 
From: "Eric" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>; <dbi-users@perl.org>
Sent: Tuesday, April 26, 2005 3:43 PM
Subject: Re: Efficient select/insert


At 01:13 PM 4/26/2005, Jonathan Mangin wrote:
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q->param();  # HTML checkboxes
foreach my $element (@array) {
  my $sql = "select col2, col3, col4 from table1
             where col1 = ?";
  my $sth = $dbh->prepare($sql);
  $sth->execute($element) or die $sth->errstr();

Do these selects first and push the results into an array of arrays. Then 
prepare your insert and loop through your array on the execute portion 
only. Short of multiline inserts that should be your fastest way to do 
things.

Doesn't this (below) affect my ability to do that?

I thought I could use fetchrow_arrayref and push an array
of arrays. The DBI docs say:
"Note that the same array reference is returned for each fetch,
so don't store the reference and then use it after a later fetch."
Sounds like I can't use that. Now I see execute_for_fetch. Does this
sound like a job for execute_for_fetch?
--J

Lead Programmer
D.M. Contact Management
250.383.8267 ext 229