All Forums Database
Soumyatg 9 posts Joined 07/08
22 Jul 2016
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?

Soumyatg
22 Jul 2016

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

M.Saeed Khurram 544 posts Joined 09/12
24 Jul 2016

You can read this thread it will answer your question.
http://datawarehouse.ittoolbox.com/groups/technical-functional/teradata-l/merge-with-nupi-index-4742107

Khurram

Soumyatg 9 posts Joined 07/08
24 Jul 2016

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.
 

Soumyatg

M.Saeed Khurram 544 posts Joined 09/12
25 Jul 2016

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

CarlosAL 512 posts Joined 04/08
25 Jul 2016

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

Soumyatg 9 posts Joined 07/08
04 Sep 2016

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.

Soumyatg

You must sign in to leave a comment.