The following example uses a nontemporal source table for the merge. Note that the values to be inserted can be any values, even those that would go into a different partition.
CREATE MULTISET TABLE bi_tgt_tbl ( pkey_field INTEGER, int2_field INTEGER, vtcol PERIOD(DATE) NOT NULL AS VALIDTIME, ttcol PERIOD(TIMESTAMP(6) WITH TIME ZONE) NOT NULL AS TRANSACTIONTIME) PRIMARY INDEX ( pkey_field ) PARTITION BY CASE_N( ((END(vtcol)) >= DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)), ((END(vtcol)) < DATE ) AND ((END(ttcol)) >= CURRENT_TIMESTAMP(6)), (END(ttcol )) < CURRENT_TIMESTAMP(6)); CREATE SET TABLE src_tbl ( pkey_field INTEGER, int2_field INTEGER) PRIMARY INDEX ( pkey_field ); SEQUENCED VALIDTIME MERGE INTO bi_Tgt_tbl USING /* This block prejoins and determines the target valid time values */ ( NONSEQUENCED VALIDTIME PERIOD (DATE'2009-12-15', DATE'2009-12-18') AND CURRENT TRANSACTIONTIME SELECT s.pkey_field, s.int2_field, END(b_t.vtcol) vtend, END(b_t.ttcol) ttend FROM src_tbl s LEFT OUTER JOIN bi_tgt_tbl b_t ON s.pkey_field = b_t.pkey_field ) AS nonbi_srct ( pkey, int2, vtend, ttend) ON (pkey_field = nonbi_srct.pkey) AND END(vtcol) = vtend AND END(ttcol) = ttend WHEN MATCHED THEN UPDATE SET int2_field = nonbi_srct.int2 WHEN NOT MATCHED THEN INSERT ( nonbi_srct.pkey, nonbi_srct.int2, PERIOD(TEMPORAL_DATE, UNTIL_CHANGED ) );