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.
Spreadsheet has following columns for a candidate
I have to insert record in 2 tables.
- Candidate table with following columns
- CandidateID (identity column Primary Key)
- CandidateJob Table with following columns
- CandidateJobID (identity column Primary Key)
- CandidateID (Foreign Key)
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
- Insert in Candidate Table (Using Process 1 above)
- 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 ?
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