Lookup generated key

Situation: Once a new record is entered in the master table the master table generates a key. I need to grab this key and insert rest of the data in secondary table.

For example

Spreadsheet has following columns for a candidate

Name

Address

Country

State

Jobid1

Jobid2

I have to insert record in 2 tables.

1. Candidate table with following columns
   1. CandidateID (identity column Primary Key)
   2. Name
   3. Address
   4. CountryStateID

1. CandidateJob Table with following columns
   1. CandidateJobID (identity column Primary Key)
   2. CandidateID (Foreign Key)
   3. JobID

I am able to insert data in Candidate Table. (let us say Process 1)

To insert the data in the CandidateJob Table I created two process flows

1. Insert in Candidate Table (Using Process 1 above)
2. Using the lookup method get the candidateid from the Candidate table and insert in CandidateJob table

Is there a way I insert record in both candidate and CandidateJob table in one process i.e. Process1 ?

Solution:

If your identity column is auto-incremented, you can definitely do it in one process in fact in one mapping. Below are the brief description of the required steps

• Create a advance database schema with the master and child table
• Load the above schema as target in the data mapping step
• In Mapper do the following steps
  • Create a global variable to do a database lookup for a query like

Select MAX(id column) from table

• Create a global variable and set it to the above fetched value + 1
• Map the master table identity column to the above global value
• Map the foreign key filed in the child table to the same global variable