All Forums Database
WAQ 158 posts Joined 02/10
16 Apr 2010
MERGE vs UPSERT

what is the difference between MERGE and UPSERT?
which one of them is supported in Teradata?

srinivasa meka 10 posts Joined 01/10
16 Apr 2010

From what I understand, Merge is SQL used to upsert data. Merge is a valid SQL command in databases such as Oracle. Never came across upsert as a valid sql command.

Coming to TD, I am not familiar with merge command. This may work for you:

UPDATE tname
SET cname = expr [... , cname = expr]
[ WHERE cond ]
ELSE
INSERT INTO tname [ (colname [... , colname] ) ]
VALUES (expr [... , expr])

A simple test with above SQL syntax can verify if this works for you.

Jim Chapman 449 posts Joined 09/04
16 Apr 2010

Teradata (as of release 12.0) supports both the ISO/ANSI standard SQL MERGE statement and the UPDATE...ELSE INSERT syntax. That latter is commonly known as "upsert".

WAQ 158 posts Joined 02/10
17 Apr 2010

Okay so teradata supports both but still can understand that whats the difference between the two?

Jim Chapman 449 posts Joined 09/04
19 Apr 2010

The MERGE statement is more general. It supports all the functionality of upsert and has some additional capabilities. MERGE is supported by the ANSI/ISO SQL standard, so it is more portable.

WAQ 158 posts Joined 02/10
19 Apr 2010

UPSERT is only the concept and MERGE is the implementation of this concept. Right?

I found MERGE syntax in many places but could not able to find any syntax for UPSERT thats probably because an UPSERT is really just an UPDATE combined with an INSERT. Is it like this?

teradata_techie 13 posts Joined 03/10
20 Apr 2010

you are correct... UPSERT is a concept using which you can attain "Slowly Chaging Dimension type-1". Which basically says that if a row exists update it with new data, if its not existing already, then insert it.
Its a concept but not a SQL command.

MERGE INTO is a SQL command using which you can achieve the UPSERT operation.

WAQ 158 posts Joined 02/10
20 Apr 2010

i think this really clear my concept regarding UPSERT and MERGE
thanks all.

MichaelJin 1 post Joined 11/09
07 Jan 2011

http://www.teradatau.courses.teradata.com/learning/BLADE_MS/legacy/29957_SQL_ApplDev/10FeaturesV2R4.1/wbt-sql020c0.htm

An 'Upsert' is an operation which performs either a row update or a row insert, depending on the pre-existence of the row. There is no 'UPSERT' command as such, rather the Upsert process is an extended form of the UPDATE command.

There are some key rules governing the use of this form:

The INSERT and UPDATE must reference the same single row of the same table.
The UPDATE must use the Primary Index of the table.
The target table may be a table or a view.

The simplest syntax of the Upsert form of the UPDATE command is as follows:

UPDATE table1 SET col_a = value_a
WHERE PI_col = PI_value ELSE
INSERT INTO table1 VALUES (PI_value,value_a);

super25 19 posts Joined 07/11
18 Jun 2013

How can i do an upsert into teradata table(DEV) by comparing PRD table. Looking for a way to synch DEV table to PRD table and make them identical as there are some changes made to the existing records and new records were also added.

listoni 5 posts Joined 02/11
08 Jul 2015

Super25,
You can't!
DEV and PROD will be different physical machines and you;ll be logged on to one or other but not both.
You'll need to do an extract from PROD, a multi-load into DEV and then do the MERGE.
 
Remember, there may be data protection issues to considerhere. Do you really want Developers to have (unsanatized) access to Production data?
 

You must sign in to leave a comment.