I am using Perl DBI with DBD::Informix but I suspect the exact database is not important to this question. Bottom line: I'm looking for a mass-insert method where I can insert the contents of a hash [of hashes] into a table in one call.
Commentary:
I have grown enamored of the Perl DBI method fetchall_hashref(), wherein I can run the query and fetch the whole blessed active set - which may be thousands of rows - into a hash in one call.
I'm reading through the POD on both of these modules, looking for some equivalent of this in an insert statement or PUT call, something like a putall_hashref() method. The best I find is a simple one-row insert, where I've PREPAREd an INSERT statement with ? placeholders and then execute the PREPAREd stament. I've used a PUT cursor available in ESQL/C (and Informix-4GL) but even those are still one row at time.
I need a mass-insert method.
Is such a method in there someplace but I've missed it?
I see the comments by Shawn and zdim.
Shawn & zdim,
Example of current (untested) code, though I've used similar stuff before:
$db_partns = $extract_smi_p->fetchall_hashref("partition");
# Pulls from temp tab in DB1
...
Now loop to insert each row from above hash into new temp table in another DB
for my $partn (keys %$db_partns)
{
$put_statement_p->execute(@{$db_partns->{$partn}}{@partn_fields});
}
Note: @partn_fields is an array of keys i.e. column names. (Using hash-slice scheme.)
To use execute_array() I'd need to separate all values in each column into a separate array. Thanks for the clever idea; I may use it some day. But to set that up is an even uglier setup than I'm already doing.
