This is very inefficient ... 1. You are prepare()ing your SQL statements in a loop. 2. You are performing row-at-a-time processing
Move your SQL statements out the loop. This will be a big performance gain (in a high transaction app). However (in Oracle), this would best be accomplished by ... insert into table2 (col_list) select col_list from table1 ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Jonathan Mangin [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 2:14 PM To: [email protected] 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 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
