Try this? As auto concatenation is happening here it is putting in single table. Using Qualify to identify the Fields of different tables loaded and will help to remove synthetic keys.
NEWLABEL_A: SELECT * from SAME_ORACLE_TABLE WHERE COL1 = 'A';
NEWTABLE_B: SELECT * from SAME_ORACLE_TABLE WHERE COL1 = 'B';
NEWTABLE_C: SELECT * from SAME_ORACLE_TABLE WHERE COL1 = 'C';
I agree with your solution that QUALIFY *; coupled with NoConcatenate will solve this problem. However, QUALIFY *; at the beginning is sufficient enough (No need to write it for every single load as well as NoConcatenate keyword).
Using only NoConcatenate will create three separate tables along with synthetic key (as the field names of three tables are same).
Hope this makes sense.
your tables are AutoConcatenated due to their common structure.
As already suggested, NoConcatenate will prevent the autoconcatenation.
Can you explain why you need those tables be separated?
Maybe there is another option.
Before leaving work I aliased all column names as this is only a proof of concept to create a data entry form to do maintenance on an Oracle table. That worked perfectly but I most certainly want to try out what you said. I'm sure it would have been less brutal. All values are being set to temp variables and used in an insert statement to populate the table. This project has me populating data different data from a template table for QA and PROD simultaneously. So far I expect will work and the inserts also work directly from an OLEDB connection.
Thanks once again for your reply.