"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 04/26/2005 05:09:23
PM:
>
> ----- 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:
<snip>
> >
> > Why not use an INSERT...SELECT instead of splitting up the two steps?
<snip>
> > my $sql = "INSERT table2 (col2, col3, col4) select col2, col3, col4
<snip>
> >
> > 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.
>
Yes but it isn't formatted as a comma separated list to be used in an IN()
comparator.
> >
> > #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?
> ;)
Good catch! I did forget the INSERT clause. BTW, If @array is a list of
numbers, you don't need the surrounding single quotes. Just build the
string (arraylist) so that each number is not quoted but is still
separated by a comma.
<snip>
>
> Excellent! Thanks a lot, Shawn.
> --Jon
No prob! Glad I could help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
