SQL MERGE Detecting Duplicate Rows

Database
Enthusiast

SQL MERGE Detecting Duplicate Rows

We are using the SQL MERGE statement as a method to perform straight inserts.  When running in Teradata transaction mode, is there a way to detect and log records that do not make it to the target? For example, records that don't get loaded because the target is a SET table and the record already exist or the record violates a USI.

Basically, for audit and control purposes, I need to evaluate the results of a MERGE INSERT any time it does not load the exact source record count even though there are some siutuations in which Teradata returns a zero SQL code.

Tags (1)
2 REPLIES

Re: SQL MERGE Detecting Duplicate Rows

We can't use Extract(HOUR from CURRENT_TIMESTAMP) in INSERT STATEMENT in MERGE. It throws an error that expression must match INSERT specification primary index and partition columns. Is there anyway to do this in some other way?

Enthusiast

Re: SQL MERGE Detecting Duplicate Rows

If you are using Merge statement in TPT Operator, then it inserts the UV violated rows into a table name with TableName_UV. You can analyse that table to get the rows not inserted due to duplicate PI.

Khurram