nextval() and sequences are not what I'm looking for. I want to
assign the same id to all the rows imported from the same file.
Let's say user A is working on portfolio_id 3, and decides to
upload a spreadsheet with new values. I want to be able to import
the spreadsheet into the staging table, and assign a portfolio_id
of 3 to all its entries.
Of course, I can't just UPDATE the staging table to have
portfolio_id = 3, because user B might also be uploading a sheet
for portfolio_id = 9.
The first thing to occur to me is to make the staging table TEMP, so
every session its own copy. But the second thing is, do you really
need a portfolio_id column in the staging table? After you get the
data massaged correctly into the staging table, perhaps you could
load it into the main table thusly:
insert into main_table (portfolio_id, other_columns ...)
select 3, other_columns ... from staging_table;
where 3 is the portfolio_id you want to assign to all the data you're
currently loading. This may not work exactly for your situation, but
does some variant make sense?
- John Burger
MITRE
