As pme asked, what SQL DB are you using? Do you have a choice? If you have a choice, I would recommend SQLite. This is my "go to" DB and it doesn't require any installation or admin of a DB Server - the "care and feeding" of a DB server can get involved. With SQLite, there are no "accounts" or "passwords", if you have permissions to access to the DB file, you can use it. One of your hassles just goes away.

I am working on an SQL application right now. While I am experimenting, my version of first.pl deletes the existing DB if it exists and starts completely anew. An alternative is the SQL: $dbh->do("DROP TABLE IF EXISTS main"); which I use in some other processing phases. This code is to show how easy it is to "get started" and create a brand new DB. 

use strict;
use warnings;
use DBI;
$|=1;

#################
## Delete the old DB (if it exits)
## and create a brand new DB
##
my $dbfilename = "MASTER.sqlite";

if (-e $dbfilename) 
{ 
   unlink $dbfilename 
           or die "delete of existing $dbfilename failed $!";
}    

# This is the basic "open DB voodoo"

my %attr = ( RaiseError => 1);  #auto die with error printout
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfilename","","",\%attr)
    or die "Couldn't connect to database $dbfilename: " . DBI->errstr;

## Ready for Business now!!
####################################

create_table($dbh);  #RAW_DATA table

my %yr2dir =( 2018 => "../some directory",
              2019 => "../some directory",
            );

foreach my $year (sort keys %yr2dir)
{
   $dbh->begin_work;
   print "working on $year in directory $yr2dir{$year}...\n";
   populate_table($dbh,$year,$yr2dir{$year});
   $dbh->commit;
   
   print "finished $year\n";
}

[download]
Of course if you need to know, help can be provided about the CREATE TABLE statements and how to do the inserts. My code is not relevant to your application. My DB is a "small DB", only about 500K 1.3M records in the main table and takes about 30 seconds to completely "start over".

I think a temp table would only persist while your DB connection that created it is open. I see no need to mess with that. Just make a regular table which will persist across any disconnect/reconnect. If you want to "clean up after yourself", then just DROP the table.

So, How can I declare the database connection parameters just once? With SQLite, you just need the DB name. For my app, I just put the name "MASTER.sqlite" as a constant in all of the programs (and yes the actual name is bit more descriptive). I could make a config file with that data in it or even account/password info if I was using a different DB.

I don't see why you would need to split the code up into 4 programs? I mean things like "third.pl will join the two temp tables from first and second and load it into third". That is one SQL statement! CREATE TABLE AS.... A separate program is a lot of overhead of one SQL statement. But the normal way to do this would be to have a shell batch file that runs each of your programs and then exits if X.pl returns a non-zero error code. Just like any other program, Perl will return 0 if program exits successfully, error code if an abend. You can force error exit with exit(476); or whatever.

Update:

I mentioned that your "create 3rd table from 2 other tables" is a single SQL statement.
Here is an example where I create a table which is actually a much, much smaller set of a larger table and it is sorted in a way that I like it for easy viewing with my GUI tool.
When you create a table from other tables, SQL already knows the data types, field lengths etc from the table(s) you are using as the "source". As I did here, you can rename the columns during that process. The SQL syntax for using 2 source tables is more complex, but this is a demo of from some actual working code:

$dbh->do("DROP TABLE IF EXISTS sent_info"); #re-calculate this critter
+!

$dbh->do("CREATE TABLE sent_info AS
          SELECT year, sent_call AS call,sent_prec AS prec, sent_ck AS
+ ck, sent_sect AS sect
          FROM raw_qsos 
          GROUP BY sent_call, year 
          ORDER BY sent_call, year DESC");

[download]
This is so fast that I haven't bothered to measure it.

Update2:

I can see situations where processing should be broken up into multiple phases. But I don't see the need that for the OP's question.

In my current app, I am trying to figure out given your complete USPS address: street address (not a PO box), zip code and state, what state do you live in? It turns out that is a very complicated question to answer! Much to my surprise! With the DB's I have, I will be able to come close to a predicted state of residence, but there will be uncertainty in that prediction. Right now I have different "phases" of DB creation while I am developing so that I can see intermediate analysis of results while I am fine tuning the heuristics (rules of thumb). I don't think the OP is trying to answer any kind of question like "what state do you live in?".

In reply to Re: designing a program - your wisdom needed by Marshall
in thread designing a program - your wisdom needed by SpaceCowboy

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.