for ( 0 .. $#fields) {
   %sql_data = %{$fields[$_]};
  ... insert into DB ...
}
[download]

foreach my $sql_data ( @fields ) {
   $stmt = qq/INSERT INTO temp_sheet (/ . join(',', keys %$sql_data ) 
+. qq/) 
               VALUES (/ . join(',', ('?') x keys %$sql_data ) . qq/)/
+;   
   $dbh->do( $sql, {}, values %$sql_data );
}
[download]

use SQL::Abstract;
my $sql = SQL::Abstract->new;
foreach my $data (@fields){
  my($stmt, @bind) = $sql->insert('temp_sheet', $data);
  $dbh->do( $stmt, {}, @bind );
}
[download]

Thanks davidrw, great answers and examples.

Strange though, I did try

%sql_data = %{$fields[$_]};
[download]

before my OP, but for some reason it didn't work. Anyway, I'm good to go...and a little smarter.


—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

If you're processing a lot of rows, you'd be better off making you perl script a little simpler: have it just print out another suitable flat file that would be easy to feed into whatever data-importer tool is native to the particular database server you are using.

The speed differential between bulk-loading with such a tool vs. doing a series of inserts with DBI (even with a prepared statement and placeholders) can be dramatic if you're dealing with thousands of rows on a regular basis. And of course, you can set up your perl script so that once it's done writing the loadable flat file, it goes ahead and runs your database bulk-loader utility on the file. That way, the end result from the user's point of view is the same, except that it happens much faster.

(For smaller sets of rows, it's a toss-up -- whatever you're most comfortable with is fine -- but the bulk-load tool scales well, whereas DBI inserts do not.)

Okay, I'm interested. Do you have sample code that show how this works? I'm especially interested in how it gets from the new flat file into the database (I use MySQL).

Thanks.


—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

In terms of how to get flat-file data into table, just read about "LOAD DATA INFILE" and "mysqlimport" in the mysql manual -- it's pretty clear and simple (a lot less hassle than the bulk-loader tools of some other RDB engines).

As for getting your perl script to write a flat file instead of doing inserts directly to mysql, just open an output file instead of a connection to the db. (Well, if some of your input is coming from the db, you can either connect to do the query, or save the query output to a file before running this script.)

When you get to the point in the script where you would have done an insert statement, just write those values to the output file as tab-delimited fields, terminated by a newliine. When done, close the file and use "system()" to run mysqlimport on it.

Why are you even doing the copy? It seems like a waste:

foreach (@fields) {
   
  my $sth = $dbh->prepare( 'INSERT INTO temp_sheet ('
                           .join(',', keys %$_)
                           .') VALUES ('.
                           .join(',', map {'?'} keys %$_)
                           .')'
                          );
  
  $sth->execute(values %$_);
}
[download]

Of course, there are still issues with that in broad strokes, because you're preparing a statement for each record, and you're relying on keys and values returning the same order. While, AFAIK, they do, I'm not sure it's a promise future versions of Perl will keep.

So, I'd refactor a touch:

# list your headers
my @heading = qw[description billing_code user_id 
                 project_id bad_proj bad_bill bad_user];

# now prepare a statement *once*
my $sth = $dbh-<prepare('INSERT INTO temp_sheet ('
                        .join(',', @heading)
                        .') VALUES ('.
                        .join(',', map {'?'} @heading)
                        .')'
                       );

# now insert
foreach my $row (@fields) {
   $sth->execute( map { $row{$_} } @heading );
}
[download]

That should be a lot faster. You could also lower your maintenance requirements by determining @heading from the DB with:

my @heading;
{
   my $sth = $dbh->prepare('SELECT TOP 1 * FROM temp_sheet');
   $sth->execute;
   @heading = @{ $sth->{NAME_lc} };
}
[download]

It's worth mentioning that the SELECT TOP 1 syntax might differ from DB to DB. In some cases, it is SELECT * FROM table LIMIT 1, and there might be others. It's a factor to consider.