At 02:13 PM 4/26/2005, Jonathan Mangin wrote:
----- Original Message ----- From: "Eric" <[EMAIL PROTECTED]> To: "Jonathan Mangin" <[EMAIL PROTECTED]>; <dbi-users@perl.org> Sent: Tuesday, April 26, 2005 3:43 PM Subject: Re: Efficient select/insert
At 01:13 PM 4/26/2005, Jonathan Mangin wrote: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();
Do these selects first and push the results into an array of arrays. Then prepare your insert and loop through your array on the execute portion only. Short of multiline inserts that should be your fastest way to do things.
Doesn't this (below) affect my ability to do that?
I don't really know. But I do know that if you do it on your own you won't have any problems. It just gets nasty like this when you try to use the *exact* ref that was returned from the query. I have run into some pretty confusing problems with refs in general like that, but for me, when in great doubt, just make a damn copy :) The only problem I would see with my suggestion is if you have a massive amount of data like hundreds of megs.
- take your big array to start with that contains your select criteria, loop through it and do the selects, pushing the results onto a "cache" array, use fetch_array() and push the reference you make from that onto your cache array.
- When you are done, prepare your insert statement
- Then do a new loop on your cache array to get your data for your insert loop on the $sth->exec(params)
This might be a little too simple for you since, you seem to be looking for a really super slick way of going about things.. I like doing fancy stuff with DBI too, but if it borks and I don't get it, I then have to go back to just getting it done :)
Of course if you have a bunch of indexes in the table you are doing your inserts on, that could be more important than a lot of this, so you might want to try dropping them at the start of the insert loop and recreating them after. I think mysql even has a function to do this for you now.
Thanks,
Eric
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
Lead Programmer D.M. Contact Management 250.383.8267 ext 229
