RE: Efficient select/insert
- From: mark.d.andrews@xxxxxxxxxxx (Mark D Andrews)
- Date: Wed, 27 Apr 2005 12:02:28 -0400
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:jon.mangin@xxxxxxxxxxx]
Sent: Tuesday, April 26, 2005 4:14 PM
To: dbi-users@xxxxxxxx
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
.
- Prev by Date: Re: DBD::Sybase returns blank character instead of empty string
- Next by Date: Re: DBD::Sybase returns blank character instead of empty string
- Previous by thread: RE: Efficient select/insert
- Next by thread: Need Help to install DBD-Oracle on Windows 2000
- Index(es):
Relevant Pages
|
|
