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.



  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 

.

Relevant Pages

  • Re: Picking Element from Array one by one
    ... >> I am getting some values from loop and i store all that value in array ... >> using push function. ... no correllation. ... Please Rita, get it together woman. ...
    (comp.lang.perl.misc)
  • Re: The prodigal son returns...more easy Homework Help...
    ... Looking at your output, you're obviously "running off the end" of the "lower" array, and printing one number from the "higher" array. ... to push and pop 16 bit values as long as the stack remain aligned ... There may be other places where you want to change from 16-bit registers to 32-bit registers, ... It "sounds logical" that 16-bit calculations would be faster and "easier for the CPU". ...
    (alt.lang.asm)
  • Please help with convoluted script
    ... I have a script which is supposed to query a database and compile data for every ... push @months, $month; ... # Build the array which will hold the dates for the previous week ... # so we can output the proper string later. ...
    (perl.beginners)
  • Re: Address of a specific element: an Array containing Array References ...
    ... the push creates another copy. ... array *ELEMENT* instead. ... wouldn't automatically dereference. ... by pushing aliases rather than references. ...
    (comp.lang.perl.misc)
  • Re: Autovivification by foreach
    ... FS> push @a,$_ for @$ref; ... there is no exception anymore. ... Perl creates the array on the fly. ...
    (comp.lang.perl.misc)