Multiple tables loaded into single target table
Posted: Wed Jan 11, 2006 1:40 am
Hi,
Need some help with this requirement
I have 3 oracle tables ( which has a sinlge pseudo key column meaning there can be duplicates) with raw data directly from Operational data
3 tables have different metadata and there is a single ID field which is suppose to be same in all the three tables
these three table have to be loaded into a single target table
For example,
Table A has KeyField, and 4 other fields
Table B has KeyField and 3 other fields
Table C has KeyFilef and 3 other fields
The taraget table should have Surrogate key(Auto-generated Number), KeyField, 4 fields from A, 3 fields from B and 3 fields from C
This is not the end to this, there cannot be duplicates -- meaning if there are 2 records with all the columns exactly same, then the second record can be rejected. If any one column differsm it has to be inserted as a new column with a new flag and numbering each new data with same KeyField
It sounds little complicated but if any of you came across this situation, help me with this
One of the way that I am thinking is --- loading these source tables into source files and using merge stage to get one single file with all the column and map it to the target with hash lookup against the target table
Am all confused, help me with this
Ragu
Need some help with this requirement
I have 3 oracle tables ( which has a sinlge pseudo key column meaning there can be duplicates) with raw data directly from Operational data
3 tables have different metadata and there is a single ID field which is suppose to be same in all the three tables
these three table have to be loaded into a single target table
For example,
Table A has KeyField, and 4 other fields
Table B has KeyField and 3 other fields
Table C has KeyFilef and 3 other fields
The taraget table should have Surrogate key(Auto-generated Number), KeyField, 4 fields from A, 3 fields from B and 3 fields from C
This is not the end to this, there cannot be duplicates -- meaning if there are 2 records with all the columns exactly same, then the second record can be rejected. If any one column differsm it has to be inserted as a new column with a new flag and numbering each new data with same KeyField
It sounds little complicated but if any of you came across this situation, help me with this
One of the way that I am thinking is --- loading these source tables into source files and using merge stage to get one single file with all the column and map it to the target with hash lookup against the target table
Am all confused, help me with this
Ragu