SAP JDBC receiver scenario - Truncate and Load

[Former Member]

Hi All,

I have a scenario where I need to truncate full table and then load full table.

What is the best design approach for this.

1. Do I create two separate interface and then use the option of maintain order at runtime in interface determination where I first call the truncate interface and then Bulk insert

2. Do I create two Statements in the JDBC reveiver root message type and in mapping first call the Delete and then Insert, like below. What if delete works and Insert fails, how do I handle that.

<root>

<StatementName1>

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>Truncate table  "New_Hires";</access>

</anyName >

  </StatementName1>

  <StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

  </StatementName2>

</root>

Thx in advance

Ravijeet

[bhavesh_kantilal]

Option 2 is the only viable option. Option 2 will make sure your entire call is in 1 single transaction. If Truncate is successful and Insert fails, the entire transaction will get rolled back.

Like mentioned by Inaki, is this something that PI should do is definitely up for discussion..

Regards

Bhavesh

[iaki_vila]

Hi Ravijeet,

If you go to the option 2 you can do the two operations transactional. I haven't used the Truncate SQL instruction and it depends that the jdbc driver for your DB works with it.

In my opinion PI is not the best choice to do a first full table load, the DBs normally provide tools for this, PI integration has more sense for the table maintenance.

Regards.