Is there any way to have a MERGE INTO statement insert rows into 2 separate tables? In the example below, t3 is a work table that will hold the value of :a when the WHEN MATCHED condition is not met. I somehow need to keep track of which values of :a are not matched and result in an inserted row rather than just an update.
(I understand that you cannot use AND in this context here, i'm using it to illustrate what I need to do)
MERGE INTO t1
ON t1.a = t2.a
AND t1.b = t2.b
WHEN MATCHED THEN UPDATE SET t1.c = current_timestamp(0)
WHEN NOT MATCHED THEN
INSERT VALUES (:a, :b, :c)
INSERT INTO t3 (:a)
You can't do that with MERGE.
Do you need the actual rows or just the count?
The number of Inserts/Updates is available in dbc.QryLogSTepsV (if it's enabled):
StepName 'MRM', RowCount = Inserted, RowCount2 = Updated
Otherwise you need an extra step with a INS t3 SEL FROM t2 WHERE a/b/c match
thanks dieter, I appreciate the response. I started with Teradata in 2011, and you are sort of a legend - I remember using your view that reverse-engineers "collect statistics" statements. brilliant.