Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Insert script generation in order

  1. #1
    Join Date
    Dec 2012
    Posts
    29

    Default Insert script generation in order

    Hi All,

    i want to generate insert scripts in an order.
    I have one parent and one child table.
    There are data in these tables and want to generate insert scripts like below.


    I am not able to generate scripts in the below order.I tried to loop .But i cannot loop in single rows.
    Can anyone give any idea ?
    How can we loop one single row and generate one row from child table.
    Then return to parent for next single row, then child etc .
    Another problem is i tried sql output step;But cant create "seq_p_table.nextval" without quotes.


    Parent
    ------------


    table_name: p_table
    Id name
    1 a
    2 b
    3 c


    table_name :c_table
    Id marks p_id
    1 10 2
    2 30 3
    3 40 1


    Id in table is a sequence.FK relations are present p_table.Id is FK to c_table.p_id
    Script should generate in a file like


    insert into p_table(id,name) values (seq_p_table.nextval,'a');
    insert into c_table(id,marks,p_id) values(seq_c_table.nextval,10,seq_p_table.currval);


    insert into p_table(id,name) values (seq_p_table.nextval,'b');
    insert into c_table(id,marks,p_id) values(seq_c_table.nextval,30,seq_p_table.currval);


    insert into p_table(id,name) values (seq_p_table.nextval,'c');
    insert into c_table(id,marks,p_id) values(seq_c_table.nextval,10,seq_p_table.currval);


    I am not getting any idea .Can anyone help to get?
    I am using GA 7.1 version


    Thanks.

  2. #2
    Join Date
    May 2016
    Posts
    280

    Default

    Pentaho Data Integration is a tool thought for Bulk Loads, so my idea would be to do all the inserts into P_TABLE in one transformation, and once finished, I would do another transformation to insert in C_TABLE, but initially in this second transformation I would have this data: c_table.marks p_table.name, so my input data would be:
    10 b
    30 c
    40 a
    Then I will add a lookup operator to translate the P_TABLE.NAME data to P_TABLE.ID data, and finally insert into the C_TABLE.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.