SpaceCowboy:

I don't have a database server installed handy, so I can't really help directly with your question. But with the example code you listed, I just wanted to offer a couple suggestions:

You might consider using placeholders in your prepared statements, as that is well-tested and can help you avoid injection attacks. Something like this:

# Use ? as placeholders for the values

my $sth2 = $dbh->prepare(<<EOSQL);
INSERT INTO HR.Tempest ( ID, NAME, TITLE, DEPT )
VALUES ( ?, ?, ?, ? )
EOSQL

while ( @row = $sth->fetchrow_array() ) {
    my ($id, $name, $title, $dept) = @row;

    # Now when executing, just pass the values you want to the execute
    # statement, and DBI will pass the properly-quoted values to the
    # DBD backend in the order specified (i.e., $id will be used for
    # the first ?, $name for the second, etc.)
    $sth2->execute( $id, $name, $title, $dept );
}
[download]

Next, you ought to leverage the database and let it do more of the work. The database is optimized for large sets of data. The cost of serializing the result of the select and shipping it to your computer, then having your computer deserialize the data handle it and then turn it into a sequence of SQL statements, serializing each one and shipping it back to the database server (who would have to deserialize each of the statements and then process them and perform the inserts) costs *vastly* more time, CPU and network bandwidth than simply telling the database server to insert the rows into your database. Something like this:

$dbh->do("
    CREATE GLOBAL TEMPORARY TABLE HR.Tempest(
        ID INT,
        NAME VARCHAR2(25),
        TITLE VARCHAR2(50),
        DEPT VARCHAR2(100)
    ) ON COMMMIT PRESERVE ROWS
");
$dbh->commit();

# Tell the database to select the rows of interest and populate the
# temporary table with them
$dbh->do("
    INSERT INTO HR.Tempest
    SELECT e.employee_id, e.first_name, j.job_title, d.department_name
+ 
    FROM   HR.employees e, HR.jobs j, HR.departments d
    WHERE  e.job_id = j.job_id
    AND    e.department_id =  d.department_id
");
$dbh->commit();
[download]

Finally, when working with SQL, I suggest you structure your statements slightly differently. Using the longer join syntax lets you tell the database how to connect the tables together, freeing the WHERE clause to specify which records to select. That helps future programmers more easily see what the statement is doing:

INSERT INTO HR.Tempest
    INSERT INTO HR.Tempest
    SELECT e.employee_id, e.first_name, j.job_title, d.department_name
+ 
    FROM   HR.employees e
    JOIN   HR.jobs j        ON j.job_id = e.job_id
    JOIN   HR.departments d ON d.department_id = e.department_id
    WHERE  <criteria to select a subset of records>
[download]

The more complex the statement, the easier it is to express the structure and make the SQL easier to comprehend. Here's a purely artificial example with multiple joins against tables in a single statement:

---- We want to find employees making over $150K/yr in marketing or sa
+les.
---- However, the department_id of marketing and sales teams are split
+ into
---- dozens of regions, so we'll have to use the boss' department to
---- narrow the list of employees.  That's fine, though, as we need th
+e
---- bosses information, too.

SELECT boss.employee_id as boss_id,    boss.first_name        as boss_
+name, 
       boss_j.job_title as boss_title, boss_d.department_name as boss_
+dept,
       emp.employee_id  as emp_id,     emp.first_name         as emp_n
+ame, 
       emp_j.job_title  as emp_title,  emp_d.department_name  as emp_d
+ept,
       emp.salary       as emp_salary

---- Standard table hookup for the boss

FROM HR.employees   boss
JOIN HR.jobs        boss_j ON boss_j.job_id        = boss.job_id
JOIN HR.departments boss_d ON boss_d.department_id = boss_e.department
+_id

---- Standard table hookup for the employee

     -- tie the employee to their boss
JOIN HR.employees   emp   ON emp.boss_id         = boss.employee_id
JOIN HR.jobs        emp_j ON emp_j.job_id        = emp.job_id
JOIN HR.departments emp_d ON emp_d.department_id = emp_e.department_id

---- Now select the employees we're looking for.

WHERE boss.department_name IN ('MARKETING', 'SALES')
AND   emp.salary > 150000;
[download]

Yes, it's an overly-contrived example, but it easily lets you separate "standard" table joining from the important selection criteria. When you stuff it all in a WHERE clause, it's difficult to sort it all out in your head. There's *no* difference at all to the SQL server, but it's a world of difference to the people who have to figure out what the statement says with a minimum of effort.

...roboticus

When your only tool is a hammer, all problems look like your thumb.