<html><body><div style="color:#000; background-color:#fff; font-family:Courier New, courier, monaco, monospace, sans-serif;font-size:10pt"><div><span>Todd,</span></div><div><span>>> Our DBA would like for me to investigate bulk inserts. </span></div><div><span>> Can you provide code snippets or an example of what you're trying to do?</span></div><div><span></span> </div><div><span>It looks like DBI supports execute_for_fetch. To me it appears that it was built for pushing data from one database to another. </span></div><div><span></span> </div><div><span>Anyway, I put this method together. It should be database portable as it uses DBI not DBD but DBDs can optimize the call under the hood which Oracle states that it does.</span></div><div><span></span> </div><div><span>Here's a method I put together to translate the data format that I use. The method uses DBIx::Array under the
hood.</span></div><span><div> </div><div>=head2 bulkinsertarrayarrayname</div><div>Insert records in bulk.</div><div> my @arrayarrayname=(<br> ["Col1", "Col2", "Col3", "Col4", ...],<br> [data1, $data2, $data3, $data4, ...],<br> [@row_data_2],<br> [@row_data_3], ...<br>
[@row_data_n],<br> );<br> my $count=$dbx->bulkinsertarrayarrayname($table, \@arrayarrayname);</div><div>=cut<br></div><div> </div><div>sub bulkinsertarrayarrayname {<br> my $self=shift;<br> my $table=shift or die("Error: table name required.");<br> my $arrayarrayname=shift;<br> die('Error: $arrayarrayname parameter must be an array reference') unless ref($arrayarrayname) eq "ARRAY";<br> my $columns = shift @$arrayarrayname;<br> die("Error: columns must be array reference") unless ref($columns) eq "ARRAY";<br> my $sql = sprintf("INSERT INTO $table (%s) VALUES (%s)", join(",", @$columns), join(",", map {"?"} @$columns));<br> my $sth = $self->dbh->prepare($sql) or die($self->errstr);<br> my $size =
@$arrayarrayname;<br> my @tuple_status=();<br> my $count = $sth->execute_for_fetch( sub {shift @$arrayarrayname}, \@tuple_status);<br> unless ($count == $size) {<br> warn map {"$_\n"} @tuple_status;<br> }<br> return $count;<br>}<br></div></span><div><span></span> </div><span><div><span>My tests for DBD::CSV and DBD::XBase pass with no issues.</span></div><div><span></span> </div><div><span> $dba->dbh->do("CREATE TABLE $table (F1 INTEGER,F2 CHAR(1),F3 VARCHAR(10))");<br> is($dba->bulkinsertarrayarrayname($table, [[qw{F1 F2 F3}], [0,1,2], [1,2,3], [2,3,4]]), 3, 'bulkinsertarrayarrayname');<br></span></div><div>Now onto performance testing! </div></span><div> </div><div><span>Thanks,</span></div><div><span>Mike</span></div></div></body></html>