You can add fields to tables easily:
a bunch of fields
,today() as PullDate
FROM whatever source you're loading
What sort of master calendar do you want? Here's a very basic one:
,date(yearstart(PullDate),'YYYY') as PullYear
,date(monthstart(PullDate),'MMM YYYY') as PullMonth
LOAD date(fieldvalue('PullDate',recno())) as PullDate
I assumed that if you loaded the file again, that was a new pull of the data, so should have a new date. If not, then what date do you want? If the data is always loaded from a file, is it the date the file was most recently modified? I suppose that makes more sense, though I still don't know if it's what you want. You could probably do this if so:
,date(floor(filetime('Your file name here'))) as PullDate
You might want to use a PullTimestamp instead of just a PullDate.
It's more that each new file will have a new file extension. Something like this:
CompetitiveSpend_060111.xls (Load/Pull date: 6/1/11)
CompetitiveSpend_071211.xls (Load/Pull date: 7/12/11)
In my load script, I'm using a wildcard recognzie this nomenclature (these data files are small enough that incremental loads would be overkill).