Thursday, August 13, 2015

SAP Hana is missing MERGE INTO so let's use UPSERT

MERGE INTO is great. SAP HANA SPS 10 has no MERGE INTO... So, let's use UPSERT - it might be fine for certain use cases, especially when your target table has no PRIMARY KEY. You can create PRIMARY KEY and UPSERT will use it to determine if the row should be inserted or updated. This example will show you how to do it:
create table a (x int, y int, primary key (x));

create table b (x int, y int);

insert into a (x, y) values (1,1);
insert into a (x, y) values (2,2);
insert into a (x, y) values (3,3);
insert into a (x, y) values (4,4);
insert into a (x, y) values (5,5);

insert into b (x, y) values (1, 10);
insert into b (x, y) values (2, 20);
insert into b (x, y) values (3, 30);
insert into b (x, y) values (40, 40);
insert into b (x, y) values (50, 50);

upsert a select * from b;

select * from a;
The last select will result in:
X;Y
1;10
2;20
3;30
4;4
5;5
40;40
50;50
So records were updated if the key has been already there and new records were inserted.

No comments:

Post a Comment