Here is the problem I am having. I am trying to perform an incremental load as I am daily adding or updating data and it would take too long to start from scratch each day. I have checked my incremental load code over and over, double checked all over the web and cannot figure out why I am unable to concatenate historical data to the new daily data.
It works fine for the first table... but the second tables do not append any historical data... does it have anything to do with ShipmentID being the key field for all 3 tables?
Is it populating the field (ShipmentID) for all the tables in the first step, and thus in the subsequent steps it assumes all the data was already there? If so, how do I remedy this?
concatenate (FilteredShipmentExtractRecord) load * FROM [..\..\..\QVCommon\TMS_FilteredShipmentExtractRecord.QVD] (qvd) where not exists(ShipmentID);
STORE [FilteredShipmentExtractRecord] INTO [..\..\..\QVCommon\TMS_FilteredShipmentExtractRecord.QVD];
drop table FilteredShipmentExtractRecord;
concatenate (F_ShipmentReference) load * FROM [..\..\..\QVCommon\TMS_F_ShipmentReference.QVD] (qvd) where not exists(ShipmentID);
STORE [F_ShipmentReference] INTO [..\..\..\QVCommon\TMS_F_ShipmentReference.QVD]; drop table F_ShipmentReference;
concatenate (F_ItemAllocationDetail) load * FROM [..\..\..\QVCommon\TMS_F_ItemAllocationDetail.QVD] (qvd) where not exists(ShipmentID);
STORE [F_ItemAllocationDetail] INTO [..\..\..\QVCommon\TMS_F_ItemAllocationDetail.QVD]; drop table F_ItemAllocationDetail;
Ok, so having re-read your question, these are my thoughts:
While you do a store into and drop on the various tables (FilteredShipmentExtractRecord, etc), you may have still concatenated the data with a previous table (in unshown code above the section you've pasted).
I think if you could share the load script it might be easier to see how you have scripted your table naming and from there, we can see how the concatenate / drop is working.