Efficient select/insert

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

  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

.

Relevant Pages

  • Re: Efficient select/insert
    ... Do these selects first and push the results into an array of arrays. ...
    (perl.dbi.users)
  • Re: Efficient select/insert
    ... foreach my $element { ... Do these selects first and push the results into an array of arrays. ...
    (perl.dbi.users)
  • RE: tough query (for me)
    ... query, I had to write VB code which used one query which obtained all of the ... would obtain data from both Col1 and Col2 into a recordset. ... Col1 and Col2 data to an array. ...
    (microsoft.public.access.queries)
  • Re: Using binding in order to pass values to a sql statement.
    ... the form of an array. ... Inside the loop, this would help you see the issue. ... You can also call execute with your bind params in the call, ... Of course you aren't doing anything with $sth here? ...
    (perl.beginners)
  • Re: doubt about upgrade
    ... can't press reset button! ... know whether I push it or not (I don't feel that I'm pressing sth). ... Push straight in, about a half inch. ... Remove the paper-clip, wait a few seconds, and then release the ...
    (comp.sys.hp48)