Re: Efficient select/insert

[Eric]

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.


  my @row = $sth->fetchrow_array;
  $sql = "insert table2 (col1, col2, col3, col4)
          values (NULL, ?, ?, ?)";
  $sth = $dbh->prepare($sql);
  $sth->execute($row[0], $row[1], $row[2]) or die $sth->errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon
Further...
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