Insert in Multiple Tables ReceiverJDBC in sequence

[Former Member]

Hello Experts,

I have an interface in which i need to insert into 4 tables, i know that is possible using receiver JDBC with multiple statement but My requirement is that data should be inserted in sequence.

Table1 then table2 then tab3 and tab4 as they have constraints.

I know that it can be achieved by using Stored Procedure, or having a staging table and then some other SP inserting data in sequence after reading staging table.

Is there any other way to achieve it ? (NO BPM). Any new functionality to achieve that in PI 7.11 ?

Regards

Inder

[baskar_gopalakrishnan2]

Since you are going to insert data into 4 tables in a sequence one after another , I see three options.

You already mentioned 2 options 1) Stored procedure and 2) creating 4 statement data structure (one for each table)

The third option is writing a SQL with join for the 4 tables and use action command = SQL_DML. Example as follows....

Write SQL code and place it in access tag. Pass values for the columns using key tag...

<stmt>

    <Customers action="SQL_DML">

      <access> UPDATE Customers SET CompanyName=u2019$NAME$u2019, Address=u2019$ADDRESS$' WHERE CustomerID='$KEYFIELD$u2019

      </access>

      <key>

        <NAME>name</NAME>

        <ADDRESS>address </ADDRESS>

        <KEYFIELD>100</KEYFIELD>

      </key>

    </Customers>

  </stmt>

Refer this [link|http://help.sap.com/saphelp_nwpi71/helpdata/en/44/7b7855fde93673e10000000a114a6b/content.htm]...

Hope this helps ....

[Former Member]

Baskar,

How can can we write table Join for insert ? Joins are for selecting data.

Regards

Inder

[baskar_gopalakrishnan2]

Search for insert into multiple tables sql ... You will get plenty of documents. Or get help from oracle developers. It is possible to insert multiple tables using when clause condition or if else condition or in other possible ways.

[baskar_gopalakrishnan2]

Refer this [link|http://psoug.org/reference/insert.html]... This is just sample... see using Insert ALL command and when condition... Oracle experts can easily do for you.

[Former Member]

Thanks Baskar. I am using SQL server so insert all is not going to work.

Thanks

Inder

[baskar_gopalakrishnan2]

Yes, Inserting into multiple rows is not fully possible in SQL Server. To some extent using merge command in the middle to achieve this. Not sure more about it. In this case, You might go for Stored Procedure or Creating seperate statements for each insert.

[agasthuri_doss]

Adding to Baskar,

Inserting into multiple rows is possible thru Stored Procedure .

Cheers

Agasthuri

[Former Member]

Baskar means to say multiple tables.. not rows. that was just typo.

[baskar_gopalakrishnan2]

Sorry for the typo. I meant only mutliple tables.... Multiple rows in the same table is simple..