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?
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.
You can read this thread it will answer your question.
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.
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.
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.
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.
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.