MERGE...WHEN NOT MATCHED INSERT into 2 separate tables

Database
Teradata Employee

MERGE...WHEN NOT MATCHED INSERT into 2 separate tables

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
USING t2
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)
AND
INSERT INTO t3 (:a)
Tags (2)
2 REPLIES
Senior Apprentice

Re: MERGE...WHEN NOT MATCHED INSERT into 2 separate tables

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

Dieter

Dieter

 

Teradata Employee

Re: MERGE...WHEN NOT MATCHED INSERT into 2 separate tables

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.