Re: Efficient select/insert

[Jonathan Mangin]

----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, April 26, 2005 3:20 PM
Subject: Re: Efficient select/insert


"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 04/26/2005 12:26:20
PM:
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

Why not use an INSERT...SELECT instead of splitting up the two steps? Is
there a reason you need to see the data before it goes into the other
table? (http://dev.mysql.com/doc/mysql/en/insert-select.html)
foreach my $element (@array) {
  my $sql = "INSERT table2 (col2, col3, col4) select col2, col3, col4
from table1
             where col1 = ?";
  my $sth = $dbh->prepare($sql);
  $sth->execute($element) or die $sth->errstr();
}
This would be even faster if you could concatenate all of the elements of
@array into a single list then you could say:
I thought that's what I already had. @array contains
selected primary keys from table 1.
#My PERL skills are non existent so you need to write this part.
#I am assuming that @array is holding a list of string values.
foreach my $element (@array) {
@araylist += "'" + $element + "',";
}
@arraylist = left(@arraylist, length(@arraylist) -1)
#that's to remove the trailing comma at the end of the list
my $sql = "select col2, col3, col4 from table1
          where col1 IN (?)";
my $sth = $dbh->prepare($sql);
$sth->execute(@arraylist) or die $sth->errstr();
Oh, I see. A List. Hmmm.
And did you forget insert or are you practicing black magic?
;)
Like I said, I have no (zero, zilch, nil) PERL skills (this is really
simple code and I still got it wrong) but you should get the idea...
That last statement should move all of your records in one big batch.
Notice I didn't INSERT to table2.col1. By leaving it out of the INSERT
clause it's as though I inserted a NULL in that column for each record and
if col1 were an auto_increment, it should count up as expected.
I noticed and wondered. Thanks for clarifying.
Generally, if you don't have to look at the data in your application
(maybe because you need to massage it in some way) don't bring it back to
your client. Let the engine handle it at the server and things will go
much faster.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Excellent! Thanks a lot, Shawn.
--Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]