SQL Merge Statement

As you know new ANSI SQL standard suggests to support MERGE statements and most of the best RDBMS systems obays it.
but may of the users don't have any idea when to use them.

I found it very very useful when comes to merge data from muliple tables. many of the realworld database systems are much deviated from a perfect normal design.
and contains much duplicacy. duplicate data intern creates problems of anomalies. i am not going to that area.. but it a real big problem.

so many cases we may have to "sync" the data in one table with data in another tables. MERGE statemet comes for our rescue.

here i am taking an example: data in EMP1 table need to in "sync" with EMP table. you can do it a simple merge statement like:

MERGE INTO EMP1 USING EMP ON EMP1.EMP_ID = EMP.EMP_ID
WHEN MATCHED then UPDATE SET EMP1.EMP_NAME = EMP.EMP_NAME
WHEN NOT MATCHED then INSERT (EMP_ID,EMP_NAME) VALUES (EMP.EMP_ID,EMP.EMP_NAME)

so Inserts or Updates will be fired into EMP1 automatically.

here we are directly picking the data from a table EMP. please see the "USING EMP" clause.
but you are not restricted by that. you can have a complex SQL select statment in that place.