CREATE TABLE transactions(
ID int,
TranValue string,
last_update_user string)
CLUSTERED BY (ID) into 5 buckets
STORED AS ORC TBLPROPERTIES ('transactional'='true');

CREATE TABLE merge_source(
ID int,
TranValue string,
tran_date string);

sql1:
MERGE INTO transactions AS T
USING merge_source AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET TranValue = S.TranValue, last_update_user = 'merge_update'
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.TranValue, 'merge_insert','123');

sql2:
MERGE INTO transactions AS T
USING merge_source AS S
ON T.ID = S.ID
WHEN NOT MATCHED THEN INSERT VALUES (S.ID, S.TranValue, 'merge_insert','123');

sql3:
MERGE INTO transactions AS T
USING merge_source AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET TranValue = S.TranValue, last_update_user = 'merge_update';