in reply to Inserting Multiple Records in DB.

$sql = "INSERT into $table ($fields) values ($values)";

An INSERT ... VALUES statement inserts only one record per execution. You're collecting your data from a structured file using nested loops, so I doubt that it can be modeled very well with a single database record.

For efficiency you can prepare an INSERT statement once, using question marks as placeholders for the individual values, then loop through all the records. On each pass through the loop, bind a new set of values to the placeholders, then call execute() on the statement handle.

In fact you need multiple INSERT statements, one for each destination table.

You may wish to prepare all your INSERT statements before you begin reading the data, then call bind and execute at all the places you have marked with # push

Replies are listed 'Best First'.
Re^2: Inserting Multiple Records in DB.
by doom (Deacon) on Feb 29, 2008 at 22:34 UTC

    An INSERT ... VALUES statement inserts only one record per execution. You're collecting your data from a structured file using nested loops, so I doubt that it can be modeled very well with a single database record.

    This isn't true at all. In standard-SQL, you can insert multiple rows of data with a single INSERT statement, and if you look at what the "joins" are doing in the OP's code, you'll see that that's what he's doing.

    Your other advice sounds about right, though. Using bind parameters (question marks) with prepare is a good habit to get into to guard against SQL-injection attacks.

[reply]
      In standard-SQL, you can insert multiple rows of data with a single INSERT statement.

      Thank you! This was news to me. I've done my due-diligence Googling now, and I see it's true.

      But when I look at the OP's code, I can't see any syntactic markers for multiple records. The multirow INSERT ... VALUES statement looks like

      INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', 
+'555-2323');

      [download]

      with a pair of parentheses for each record to be inserted.

      Thanks also to pc88mxer below for the diligent analysis of the parsing routine, confirming that there should be multiple target tables.

[reply]
[d/l]
Re^2: Inserting Multiple Records in DB.
by Anonymous Monk on Feb 29, 2008 at 22:30 UTC
    OK, to each foreach loop doing one line at a time with
    $dbh->do("INSERT INTO test (overlay,billa,billb) VALUES ('$overlay','$billa','$billb')");
    is duplicating the records in the database, because of the nesting on the foreach loops.
[reply]
[d/l]
      Well, you could set-up the database with appropriate uniqueness constraints to prevent you from accidentally inserting duplicate rows...

[reply]
Re^2: Inserting Multiple Records in DB.
by Anonymous Monk on Feb 29, 2008 at 22:50 UTC
    Any code sample?
[reply]

In Section Seekers of Perl Wisdom