Hello,
I got a requirement where I have to load a large amount of records through JDBC connections in daily basis and insert those records into a specific table and create a relationship of records. Let me give you an overview what I need to do.
For example, the structure of the two tables is as below in which we need to load the data and then create a mapping between two.
1. Table A
Column - Name - String
Column - Number - String@@
2. Table B
Column - Account - Reference to A.Name
Column - FName - String
Column - LName - String
Let's say there are 5 columns of the external db that I need to import and need 1 column data to populate A.Name and rest of the 4 columns data are needed to populate Table B. Now to populate B, I need to create a mapping as B.Account refers to A.Name. Record needs to be created/updated into B, if any source data changes.
I would like to know how shall I approach this implementation considering below facts
1. The source data is huge - 100,000+
2. I need to run the import daily basis
3. I may not be able to import the delta changes in source system as source does not maintain a datetime field which captures changing of record and hence won't be able to use 'Use last run datetime' functionality in JDBC Data Source
4. I need to decide if a new record needs to insert or existing record needs to update into B
5. And finally create a mapping between A & B using B.Account = A.Name
Shall I write a BR on A, which would insert/update into B and create a mapping OR
Shall I write a schedule job which would do all this processing OR
Shall I write Transform Script to achieve this OR
Is there any other means to achieve this?
Please let me know what should be the best approach without impacting the performance of the system.
Thanks,
Sudipta
