Delta loading between two HANA tables

[Former Member]

Hi All,

I have two tables, both have same table definition

1) Full_Target_Table

2)Delta_Target_Table

Delta table will be truncated and loaded with daily data.

My requirement is to load the Full_Target_Table (Insert/Update) using the delta table. Need to update if any records with same natural key exists or insert  if didn't exits in the Full_Target_Table.

I am thinking of stored procedure to loop through the delta table records one by one  and upsert/replace in the Full_Target_Table

As the tables are really huge(both delta and full), looping through each record in delta table could be intensive operation.

Is there any other way? Any suggestion to improve the performance ?

Thank you !!

[former_member182302]

Hi Suman,

Adding to all the discussions, do have a look on this blog:

Regards,

Krishna Tangudu

[lbreddemann]

Hi Suman,

sounds like your requirement can be fully covered with the REPLACE | UPSERT - SQL Reference - SAP Library command.

It does exactly that: insert if the key is not already present and replace the record if it is.

.- Lars

[Former Member]

Thank you Lars,

I have primary key(PK)(Sequence generated) on FULL table. and Natural key(NK) in DELTA table.
so I end up writing like this

Upsert Table_Full select F.* from Table_full F, Table_Delta D  where D.NK = F.NK

I also tried created a procedure which will delete the Full_table records and Inserts all Delta_Table records again

Delete from Full_Table where NK in (Select NK from Delta_Table D)

Insert into Full_table  Select * from Delta_table

Which method is more efficient ?

or is there any other better way ?

[lbreddemann]

Ok, now I am not really sure what you want to do.

UPSERT cannot delete records for you. It's not   the equivalent of the MERGE command you may know from DB2 or Oracle DBMS.

You wrote that your "FULL" table has a generated primary key and the "DELTA" uses a natural primary key. So in fact there is no direct matching between both of them (otherwise you would just keep the natural key from the "DELTA" table...)

Nevertheless you go and join the two data sets in the subquery on that key.

At this point it's time to take a step back and come up with more information on your actual tables, the data load requirements. Deletion for example needs to be specifically designed for a delta mechanism.Typically data warehouses don't delete data but timestamp it to allow reporting with a time dimension.

So before looking at what's more efficient, let's look into what is the correct solution first, ok?

- Lars

[Former Member]

Thank you Lars!

Sorry for the confusion created .Let me explain it with more details.

Agreed Upsert and (Delete and Insert ) are not technically same . But in my case they both produce the same result.

Delta Table only have natural key(No PK defined) and is truncate and load for every batch. Full load have all the fields of Delta table with direct mapping including NK. In addition to these fields, It also has a surrogate key (sequence generated key) which is defined as primary key.

Every record in the delta has to be either insert (New rec as per NK ) or update (existing in FULL as per NK). So for every record in delta table I need to look up in FULL target table to find whether to insert or update. instead of that If I could delete all the records in FULL table having NK of delta records and insert all Delta recs in FULL tables.

Regards,

Suman.

[former_member201560]

Hi Suman,

For your requirement "My requirement is to load the Full_Target_Table (Insert/Update) using the delta table. Need to update if any records with same natural key exists or insert  if didn't exits in the Full_Target_Table."

1. You can use upsert command. It works based on the primary key. If any records already available it will update, if no records available it will do insert. Please check the SQL reference guide on upsert command for more reference.

2. If the delta table is huge, you have to use cursors inside the stored procedure. But if you use cursors, performance will be low, because cursors will process only one row at a time.

3. This kind of requirement will be easily done if you have BODS (Business Objects Data Services) tool.

Regards,

Yogesh

[Former Member]

Hi Yogesh,

Thank you for your reply.

We do not have primary key in the delta table. Primary key is generated in the Full_target_table while inserting using database sequence. So this logic should work using Natural keys.

I am exploring the BODS option as well.

Regards,

Suman.