"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:
#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();
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.
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
