Branching and recombining a stream

[bobdinkel]

I need to take data from one db and use it to update records in a table in another db. If one of the records in the source db doesn't have a corresponding entry in the table that needs updating, I need to insert a new record. However, foreign key constraints prevent me from just inserting new records (and rightly so). This foreign key (contact_id) comes from a table called "contact". So first I need to insert a new record in the contact table and capture the auto-generated contact_id. Once I've got this contact_id, I can use it to insert a new record into my destination table without running afoul of foreign keys issues.

My problem is that I haven't figured out how to combine the data from the source db with my contact_id into one insert/update.

I've tried having two hops converge on one step. I've tried putting data from the source db into variables so I could retrieve the data later. I just haven't figured it out. I'm pretty new to Kettle so any guidance would be greatly appreciated. Thanks.

[sboden]

Problem is in getting the data flowing through, so from your description I would make it in 1 job and 2 transformations, the job running job 2 transformations in sequence.

The first transformation executing the required stuff and updating the contacts table, with database lookup on your main destination table to check whether a new contact is need or not.

The second transformation executing the required stuff and updating the main destination table with database lookup on contacts ... after executing the first transformation you should be 100% sure all required rows are in your contacts table.

Regards,
Sven

[bobdinkel]

Thanks, Sven. That's exactly the sort of guidance I needed.:)