Relational data insert

[Anonymous]

Hello experts,

looking for best possible approaches for the below scenario.

I have one source contains denormalized data like ( customerid, prouductid, paymentid and too many columns for details).

The idea is: load the data into three table like customer, product and payment from the above one table.

but: i need to load customer table first and get the primarykey(identity) and productid for inserted record -> and send those product ID to where clause of another extraction to extract products of this inserted customer and -> insert customerprimarykey and productid into Product table.

i tried using tMap but it the extraction taks all records and join and give expected result.

But it takes too long as i have 10m records: 

i want to pass the product id from inserted customer table to Product extraction query at run time once the customer flow complted.

[manodwhb]

I suggest you to create a each job for each table and do the look-up to the key where it is required but you need to execute the jobs in order like parent table first and then child table next.

[Anonymous]

Dear Mano,

Thank you for replyimng.

I am doing the same and works fine but, the issue is

job1:

Source -> load only customer id and name into destination customer table "Customer" ( pkey, cusid,...)

job2:

Source -> select only products for cusid inserted from job1 and do rest.

what i want is:

job2 select query let say:

now: select productid from Source -> tmap gives inserted customer cusid and do the lookup 

what is happening : the select query take all records and do the lookup and give only related record

but it takes too long

what needs: pass the inserted cusid from job1 to where clause of job2

[manodwhb]

What is your target do? I

[Anonymous]

target insert only: 

job1: insert customer info to customer table

job2: insert product info along with pk of the customer table into product table

job1: source query: select cusid from source_tbl

job2: source query: select productid from source_tbl where cusid=job.customer.cusid (want to achieve)

can i save all the inserted cusid to file and then pass the file values to where condition of job2 query?

if so pls advise the approach

[manodwhb]

You can save into file and you need to read that file and pass record by record how many customerid will you have? What the target database Type?

[Anonymous]

thank you Mano for your time.

bit detail:

step1: loads 1m customer info to customer table ( cus_pk, cus_id, cus_contact). note: cus_pk is identity column 

step2: load the product into product tables which has relation with customer. (pro_pk, cus_key, p_name) note: pro_pk is identity column

source table: 

cus_id | cus_contact | pro_id | pro_name

100 | C1 | 98 | MB_GR1

100 | C1 | 99 | MB_GR2

destination:

Parent table: customer 

cus_pk | cus_id

1 | 100

Child table: product

pro_pk | cus_key 

1 |1

2 |1

will it be possible using tMap?

[manodwhb · Accepted solution]

Yes it is possible using tmap, first you need to take unique custid and load into customer table while inserting generate cus_key in tmap and in another job load products information ,do look-up with customer table and source to get cust_key.