Archives of the TeradataForum
Message Posted: Sun, 24 Jul 2005 @ 14:21:22 GMT
| Subj: | | Re: UPSERT Vs MERGE |
| |
| From: | | Dieter Noeth |
Aananth.S wrote:
| | I was going thru the UPSERT and MERGE statement documentations. Have the below doubts. experts pls help. Pls correct me if my understanding
is incorrect | |
| | 1. What is the difference between UPSERT (update else insert) and MERGE except that with MERGE, you can insert a row with a different PI
than the one mentioned in the ON clause? | |
Upsert is old Teradata SQL whereas MERGE is Standard SQL.
| | 2. Why is that you can MERGE only one row at a time?Why is it that the USING clause should only retrun a single row? | |
It was easier to implement :-)
| | Cant we use a MERGE statement to process many rows from say staging table to the fact table in one shot. | |
According to Standard SQL we can :-)
| | Is one restricted to ML or TPP for such tasks? | |
Yes, until ANSI MERGE is fully implemented in Teradata
| | Can anyone clarify on the reason for having such restrictions on MERGE? | |
It was easier to implement :-)
| | Is it a limitation of TD wrt MERGE. | |
Yes
| | In Oracle, you could process many rows in a single shot using MERGE and the ON does not need to be a PI/Pk (or any index for that
matter) | |
AFAIK NCR's working on it, but i don't know when it will be available.
Dieter
| 
