1. prepare_cached() the initial query
2. Loop over the initial query. (Using IN instead of = ...)
3. Use an array slice in the second execute() statement. (@assign[0..11] instead of $assign[0], $assign[1], etc.)

Essentially, it would boil down to this:

my @audit_params = keys %$audits;
my $audit_params_string = join ',', ('?') x @audit_params;
my $audit_sql = <<__END_SQL__;
SELECT AuditID, Referrals.1, Referrals.2, Referrals.3,
                              Referrals.4, Referrals.5,
                              Referrals.6, Referrals.7, Referrals.8,
                              Referrals.9, Referrals.10, 
                              Referrals.11, Referrals.12,
                              Referrals.13, Referrals.14, States.1
                         FROM   Referrals
                         INNER JOIN States On (Referrals.10 = States.1
+)
                         WHERE AuditID IN ($audit_params_string)
__END_SQL__

my $audit_sth = $dbh->prepare_cached($audit_sql) || die "Cannot prepar
+e() '$audit_sql'\n";
$audit_sth->execute(@audit_params) || die "Cannot execute() '$audit_sq
+l' with '@audit_params'\n";

my $insert_sql = <<__END_SQL__;
INSERT INTO report_printing_table
                          (1,2,3,4,5,6,7,8,9,10,11,12,13)
                       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)
__END_SQL__

while (my @assign = $audit_sth->fetchrow_array)
{
    my $audit_id = shift @assign;
    my $insert_sth = $dbh->prepare_cached($insert_sql) || die "Cannot 
+prepare() '$insert_sql'\n";
    $insert_sth->execute($audit_id, @assign[0..11]) || die "Cannot exe
+cute '$insert_sql' with '$audit @assign[0..11]'\n";
    $insert_sth->finish;
}

$audit_sth->finish;
[download]

FYI: Code is completely untested.

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

I'm not sure with access, but with MySQL I would try something like (extremely untested):

    my $updh = $dbh->prepare(q{
       INSERT INTO report_printing_table
       (1,2,3,4,5,6,7,8,9,10,11,12,13)
       SELECT AuditID, Referrals.1, Referrals.2, Referrals.3,
       Referrals.4, Referrals.5,
       Referrals.6, Referrals.7, Referrals.8,
       Referrals.9, Referrals.10, 
       Referrals.11, Referrals.12,
       FROM   Referrals
       INNER JOIN States On (Referrals.10 = States.1)
       WHERE AuditID = ?
    });

    foreach my $audit (keys(%$audits))
    {
        $updh->execute($audit);
    }
    $updh->finish;
[download]

Looks like Access does support INSERTing based on SELECT, but thats just based off a quick google, not experience. Also, why are you selecting more columns than you insert? Or is the results of the select also needed somewhere below the snippet that was posted?

Thanks,

techy

Well, for starting, your code is giving away too much.

Calling your columns 1,2,3 ... was simply brilliant, but I would go one step further and call the tables Table1, Table2, just in case some meaning is still available.

Furthermore, assigning column 1 to $audit and then column 2, 3, 4 to $assign[0] , $assign[1], $assign[2] is another recipe for success.

Now if you just moved this node to Obfuscation, everything would be perfect.

Possibly so, I can't decipher whether it's somethin I'm doing in my Perl code, or if it's something to do with Access. I was asking for help with the Perl portion, if in fact that is what was the matter. thanks for the link though.

P.S. First person with the correct answer gets a booster seat for their birthday *cue evil music*

I don't get why you prepare the SELECT statement inside the loop. There's nothing variable in it... So put the prepare phase upfront.

Also, you don't need separate prepare/execute/finish statements for an UPDATE query. You can replace it with one single do() (the DBI method).

Alternatively, again, prepare the handle for the UPDATE query outside the loop. The code could look a bit like this:
Version 1:

    $query = q{SELECT Referrals.1, Referrals.2, Referrals.3,
                      Referrals.4, Referrals.5,
                      Referrals.6, Referrals.7, Referrals.8,
                      Referrals.9, Referrals.10, 
                      Referrals.11, Referrals.12,
                      Referrals.13, Referrals.14, States.1
                      FROM   Referrals
                      INNER JOIN States On (Referrals.10 = States.1)
                      WHERE AuditID = ?};
                         
    my $sth = $dbh->prepare($query);
    foreach my $audit (keys(%$audits))
    {
        $sth->execute($audit);
        my @assign = $sth->fetchrow_array;
        $sth->finish();
            
        $dbh->do(q{INSERT INTO report_printing_table
                   (1,2,3,4,5,6,7,8,9,10,11,12,13)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)},
          undef, $audit, @assign);
     }
[download]

or Version 2:

    $query = q{SELECT Referrals.1, Referrals.2, Referrals.3,
                      Referrals.4, Referrals.5,
                      Referrals.6, Referrals.7, Referrals.8,
                      Referrals.9, Referrals.10, 
                      Referrals.11, Referrals.12,
                      Referrals.13, Referrals.14, States.1
                      FROM   Referrals
                      INNER JOIN States On (Referrals.10 = States.1)
                      WHERE AuditID = ?};
                         
    my $sth = $dbh->prepare($query);
    my $updh = $dbh->prepare(q{INSERT INTO report_printing_table
       (1,2,3,4,5,6,7,8,9,10,11,12,13)
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)});

    foreach my $audit (keys(%$audits))
    {
        $sth->execute($audit);
        my @assign = $sth->fetchrow_array;
        $sth->finish();
            
        $updh->execute($audit, @assign);
    }
[download]

There's no need for a finish() call for an UPDATE query.

Maybe, just maybe, this also fixes your problem.

I am not sure if this is a typo or not but you may want to look at this bit of code ...

foreach my $audit (keys(%$audits))
{
     
    $query = q{
         ...
    );
                         
    my $sth = $dbh->prepare($query);
    sth->execute($audit);
    ...
[download]

It looks like you are setting up to run $query but you execute $audit. Maybe that is what you intended, but maybe its a typo.

Well this stinks. dws has just pointed out that $audit is the query's parameters and not a typo.



Plankton: 1% Evil, 99% Hot Gas.