Is there a reason you need to make the round trip from the database, through your program, and then back to the database? If not then just do an insert select:
my $sql = "insert into table2 select null, col2, col3, col4 from table1 where col1 = ?"; $dbh->do($sql); -----Original Message----- From: Jonathan Mangin [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 4:14 PM To: dbi-users@perl.org Subject: 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
