All Forums Database
tstrick4 21 posts Joined 04/12
04 Jun 2013
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)

 

dnoeth 4628 posts Joined 11/04
04 Jun 2013

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
 
 

Dieter

tstrick4 21 posts Joined 04/12
05 Jun 2013

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.

You must sign in to leave a comment.