Skip to main content
Announcements
A fresh, new look for the Data Integration & Quality forums and navigation! Read more about what's changed.
cancel
Showing results for 
Search instead for 
Did you mean: 
michaelh1
Contributor II

Save to parent and child table (foreign key)

Hi i have following problem:

I am parsing XML document to store these data into to two tables Author and Book:

Parent table Author (it works fine) and at the same time persist into child table Book with attribute author_id (fk) from Author table.0683p000009Ma5e.png

 

Persistng author works fine so far (blue arrow show component that persist author), but how can i continue in persisitng books with author_id as foreign key, how can i get the ID of persisted author?

 

P.S. I am using postgresql DB so tlastinsertedID doe not work for me.

Labels (2)
1 Solution

Accepted Solutions
manodwhb
Champion II

@michaelh , Yes create separate jobs.

View solution in original post

5 Replies
manodwhb
Champion II

@michaelh, Yes you do not have lastInsertid component for PostgreSQL, so you need to design two jobs one for author and another one for Book. while loading Book you need to join with the author to get author_id  and insert into Book table.

michaelh1
Contributor II
Author

manodwhb can you please give me some hint how to achieve this?

 

so letas say i have parsed xml with books and authors with tXMLMap component i have two outputs:


0683p000009Ma9B.png0683p000009Ma9G.png

out1-authors

out2-books 

(please see attached pictures)

 

how should i continue in flow after tXMLMap component? (how to store authors and books at the same time(books have author_id as FK))

 

manodwhb
Champion II

@michaelh, you need to separate two flows and read source file two separately. 

michaelh1
Contributor II
Author

@manodwhb so firstly i have to parse authors save the and then parse books and save them right? 

but last thing is that i need this to be done in one transaction so is this pattern ok?

manodwhb
Champion II

@michaelh , Yes create separate jobs.