Merge into operation

Database
Enthusiast

Merge into operation

I am rewriting an insert statement which was taking lot of time, as a Merge statement. The target table is Multiset also it has a composite NUPI. the source may have non unique rows. Is it at all possible to achieve this by using Merge statement?

7 REPLIES
Teradata Employee

Re: Merge into operation

I think it's possible. Don't such see any such constraints why it won't be possible. But got to mention all the tgt PI columns in the join condition.

Agnit

Enthusiast

Re: Merge into operation

Enthusiast

Re: Merge into operation

Thanks Khurram, but as I understand that apart from the NUPI for the join  condition, need to specify a unique set of columns(like a Primary key) for merge operation. In my case there is no set of columns like a primary key. And if we are expecting duplicates and the join condition does not match, it will not even insert, which is not what is desired.

Enthusiast

Re: Merge into operation

Merge Statement requires Primary Key in its condition regardless of Index.

If it is not possible to define a primary key for the table, you can not use merge syntax.

you can try Upsert syntax instead.

UPDATE TableA

SET Col1 = 1 (You can not update PI of the table)

WHERE Col1 = 10 

ELSE 

INSERT INTO TableA (ID,Name) VALUES (?,?);
Khurram
Junior Supporter

Re: Merge into operation

Hi.

Teradata MERGE INTO DOES NOT require a primary key. It requires that the target  table PRIMARY INDEX columns (and partitioning columns if PPI) are present in the match condition (equality).

If more than one destination table row meets the WHEN MATCHED condition for a source row (f.ex.: NUPI), they all will be UPDATED or DELETED by the MERGE INTO.

Cheers.

Carlos

Enthusiast

Re: Merge into operation

Thanks Saeed and Carlos for the clarification. I was comparing an insert against a merge into and it appeared that while CPUTime for Merge(with only when not macthed clause) was less, the spool usage was more. Is this a know fact or is it something like an exception.

Enthusiast

Re: Merge into operation

I have a situation, wherein we first delete data on basis of YEaMnthId and sourceCd, then insert data from a temp table for the same Mnth and SrcCd. This step was taking more than 2 hours in Prod, with target table having 7 years of data. The target table is partitioned on SourceCd and YearMntId. We simulated in test environment with 3mnths of data and run both Insert into and Merge into(with not matched clause only) and found some improvement in AmpCPUTime. But the technical team has questioned if Merge into will help if the table is multiset and partitioned and the insert is straightforward with no calculations. Need your suggestions, if we should implemet merge into in Production.