Re^2: How to Do multiples SQL Insert at one time?

I totally agree.It is better to drop the index (modify the table to 'heap') first and then do the inserts thus avoiding the overhead of updating the indexes. Then assuming that you are reading from the log file and that each line has the following format $line="value1:value2:value3" then you can use placeholders:

open(FILE,"logfile.txt")||die;
while($line=<FILE>){
($field1,$field2,$field3)=split(/:/,$line);
$sth = $dbh->prepare ('INSERT INTO tablename (fieldname1,fieldname2,fi
+eldname3) VALUES(?,?,?)');
$sth->execute ($field1,$field2,$field3);
}
$sth->finish ();
[download]

as roboticus points out, it might be better to group the inserts and do a commit let's say every 500 records. you might also check for errors when executing the statement and rollback the transaction when one occurs

Comment on Re^2: How to Do multiples SQL Insert at one time? Download Code

Replies are listed 'Best First'.
Re^3: How to Do multiples SQL Insert at one time? by Tux (Canon) on Jan 08, 2008 at 07:27 UTC
I hope you don't really use code like this. In case to have DBI do bulkloading faster, you should also write faster code. That prepare should be outside the loop `open my $log, "<", $logfile or die "$logfile: $!"; my $sth = $dbh->prepare ("insert into foo values (?,?,?)"; while (<$log>) { chomp; $sth->execute (split m/:/, $_); } $sth->finish; $dbh->commit;` [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l]
Re^4: How to Do multiples SQL Insert at one time? by Juerd (Abbot) on Jan 08, 2008 at 19:54 UTC
Alternatively, let someone else separate preparation and execution! `use DBIx::Simple; my $db = ...; while (<$log>) { $db->query('INSERT INTO foo VALUES (??)', split /:/); }` [download] This prepares the query only once. Juerd # { site => 'juerd.nl', do_not_use => 'spamtrap', perl6_server => 'feather' }	[reply] [d/l]