Truncate table and mass upload with CAP

[hh]

Hello everyone,

I want to replicate a table with CAP. The entire table is to be “copied” with each run. This means that I first have to delete all entries from the target table and then write all rows from the source table to the target table.

If I do this with the standard CRUD operations, it takes quite a long time (there are approx. 165000 rows in the table).

Is there a possibility to do a “TRUNCATE” on the DB table and then a “mass upload”?

Many thanks and best regards
Heinrich

[hh · Accepted solution]

if anyone is looking for the same problem. With a short hint of @alperdedeoglu18 i get a solution with hana stored procedures.
you can find a tutorial for it here: https://developers.sap.com/tutorials/hana-cloud-cap-stored-proc..html

BR

Heinrich

[akuller · Accepted solution]

Hi @hh the better question is, why delete everything? What is the use case? I can think of several possibilities that are more efficient.

[hh · Accepted solution]

Hi @akuller, yes that is not the most efficient way in the sight of performance, but in this case it was the requirement 🙂

[christian_noack · Accepted solution]

Be aware that truncate might not work with hana & foreign key constraints, see this question: https://community.sap.com/t5/technology-q-a/truncate-not-supported-for-tables-with-quot-disabled-quo...

Also, if you want to implement this in Java, the handlers return type has to be void and you have to set the result to the context

@On(event = MethodNameContext.CDS_NAME)
public void onImportCompleted(MethodNameContext context) {
    jdbcClient.sql("Call \"some_procedure\"()").update();
    context.setResult(true);
    context.setCompleted();
}