Update_Insert - 1 query with multiple records

[former_member474221]

Hi

I have a file coming with say 50 records ...each record having a key field ( order num and order Item )

I also know how to define a target JDBC structure...

My Q is is there a way that I can fire a single UPDATE_INSERT query to the data base with this 50 records..

I do not want to repeat the STATEMENT node and create 50 seperate queries...

Is this possible

[former_member854360 · Accepted solution]

Hi,

Refer this for your Query

[former_member474221 · Accepted solution]

Hi

My query is UPDATE _INSERT so will I have to repeat ACCESS as well as KEY node...

how will the query be generated in this case..???Will it create multiple UPDATE_INSERT queries

[former_member854360 · Accepted solution]

As docu says,

action=UPDATE_INSERT

The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

The response document has the following format; one of the two values is always 0 because either an UPDATE or an INSERT action is always executed:

<update_count>count</update_count>

<insert_count>count</insert_count>

[former_member474221 · Accepted solution]

My q is do I need to repeat the STATEMENT node in order to create multiple queries in case of UPDATE_INSERT???

in case of INSERT you just need to repeat the ACCESS node and it works....but incase of UPDATE_INSERT you also have a KEY node

...Guys can anyone help

[former_member474221 · Accepted solution]

any updates??

[Former Member · Accepted solution]

You need to repeat the STATEMENT node to update/insert multiple rows. Map the Access to a constant 1.

For UPDATE_INSERT, since you have key fields, so repeating the ACCESS node will not work.

Hope this answers your query.

Regards

[Former Member]

there is one other option using which u can pass entire records at a single go...u have to change ur design use Stored procedures on the target side and pass ur entire source input as xml string input to the SP.....

If u r using PI7.1 refer this:

/people/william.li/blog/2008/02/14/sap-pi-71-mapping-enhancements-series-copy-xml-subtree

on the DB side ur database team can parse that xml input string and do the necessary operations...