Oracle merge into usage

In many cases, the following situation occurs: if a piece of data already exists in the table, update it. If it does not exist, insert new data. if you do not use the merge syntax provided by Oracle, you may first need to query the database select to see if it exists and then decide how to operate it. In this case, you need to write more code, at the same time, the performance is not good, it needs to go back and forth to the database twice. if merge is used, an SQL statement can be completed.

1) Main Functions
Provides conditional updates and inserts of data into database tables.
If this row exists, execute an UPDATE operation. If it is a new row, execute the INSERT operation.
-Avoid separate updates
-Improved performance and ease of use
-Useful in data warehouse applications


2) the syntax of the MERGE statement is as follows:


MERGE [hint] INTO [schema.] table [t_alias] USING [schema.]


{Table | view | subquery} [t_alias] ON (condition)


When matched then merge_update_clause


When not matched then merge_insert_clause;

3) Example

MERGE INTO TABLE TARGETUSING (SELECT '111111111' STORE_NO,              '2014-01' TARGET_YM,              '1' TARGET_D01,              '2' TARGET_D02,              '2' TARGET_D03,              '2' TARGET_D04,              '2' TARGET_D05,              '2' TARGET_D06,              '2' TARGET_D07,              '2' TARGET_D08,              '2' TARGET_D09,              '2' TARGET_D10,              '2' TARGET_D11,              '2' TARGET_D12,              '2' TARGET_D13,              '2' TARGET_D14,              '2' TARGET_D15,              '2' TARGET_D16,              '2' TARGET_D17,              '2' TARGET_D18,              '2' TARGET_D19,              '2' TARGET_D20,              '2' TARGET_D21,              '2' TARGET_D22,              '2' TARGET_D23,              '2' TARGET_D24,              '2' TARGET_D25,              '2' TARGET_D26,              '2' TARGET_D27,              '2' TARGET_D28,              '2' TARGET_D29,              '2' TARGET_D30,              '2' TARGET_D31,              1 USER_ID         FROM DUAL) TEMPON (TARGET.STORE_NO = TEMP.STORE_NO AND TARGET.TARGET_YM = TEMP.TARGET_YM)WHEN MATCHED THEN  UPDATE     SET TARGET.TARGET_D01  = TEMP.TARGET_D01,         TARGET.TARGET_D02  = TEMP.TARGET_D02,         TARGET.TARGET_D03  = TEMP.TARGET_D03,         TARGET.TARGET_D04  = TEMP.TARGET_D04,         TARGET.TARGET_D05  = TEMP.TARGET_D05,         TARGET.TARGET_D06  = TEMP.TARGET_D06,         TARGET.TARGET_D07  = TEMP.TARGET_D07,         TARGET.TARGET_D08  = TEMP.TARGET_D08,         TARGET.TARGET_D09  = TEMP.TARGET_D09,         TARGET.TARGET_D10  = TEMP.TARGET_D10,         TARGET.TARGET_D11  = TEMP.TARGET_D11,         TARGET.TARGET_D12  = TEMP.TARGET_D12,         TARGET.TARGET_D13  = TEMP.TARGET_D13,         TARGET.TARGET_D14  = TEMP.TARGET_D14,         TARGET.TARGET_D15  = TEMP.TARGET_D15,         TARGET.TARGET_D16  = TEMP.TARGET_D16,         TARGET.TARGET_D17  = TEMP.TARGET_D17,         TARGET.TARGET_D18  = TEMP.TARGET_D18,         TARGET.TARGET_D19  = TEMP.TARGET_D19,         TARGET.TARGET_D20  = TEMP.TARGET_D20,         TARGET.TARGET_D21  = TEMP.TARGET_D21,         TARGET.TARGET_D22  = TEMP.TARGET_D22,         TARGET.TARGET_D23  = TEMP.TARGET_D23,         TARGET.TARGET_D24  = TEMP.TARGET_D24,         TARGET.TARGET_D25  = TEMP.TARGET_D25,         TARGET.TARGET_D26  = TEMP.TARGET_D26,         TARGET.TARGET_D27  = TEMP.TARGET_D27,         TARGET.TARGET_D28  = TEMP.TARGET_D28,         TARGET.TARGET_D29  = TEMP.TARGET_D29,         TARGET.TARGET_D30  = TEMP.TARGET_D30,         TARGET.TARGET_D31  = TEMP.TARGET_D31,         TARGET.OPT_COUNTER = TARGET.OPT_COUNTER + 1,         TARGET.UPDATE_BY   = TEMP.USER_ID,         TARGET.UPDATE_DATE = SYSDATEWHEN NOT MATCHED THEN  INSERT  VALUES    (SEQ.NEXTVAL,     TEMP.STORE_NO,     TEMP.TARGET_YM,     TEMP.TARGET_D01,     TEMP.TARGET_D02,     TEMP.TARGET_D03,     TEMP.TARGET_D04,     TEMP.TARGET_D05,     TEMP.TARGET_D06,     TEMP.TARGET_D07,     TEMP.TARGET_D08,     TEMP.TARGET_D09,     TEMP.TARGET_D10,     TEMP.TARGET_D11,     TEMP.TARGET_D12,     TEMP.TARGET_D13,     TEMP.TARGET_D14,     TEMP.TARGET_D15,     TEMP.TARGET_D16,     TEMP.TARGET_D17,     TEMP.TARGET_D18,     TEMP.TARGET_D19,     TEMP.TARGET_D20,     TEMP.TARGET_D21,     TEMP.TARGET_D22,     TEMP.TARGET_D23,     TEMP.TARGET_D24,     TEMP.TARGET_D25,     TEMP.TARGET_D26,     TEMP.TARGET_D27,     TEMP.TARGET_D28,     TEMP.TARGET_D29,     TEMP.TARGET_D30,     TEMP.TARGET_D31,     NULL,     DEFAULT,     DEFAULT,     NULL,     TEMP.USER_ID,     DEFAULT,     NULL,     NULL);

Help:

Check whether there are so many fields in the database table, and the copy and write operations are both troublesome and error-prone. You can perform the following operations:

1. First query all fields in the table

SELECT COLUMN_ID,       COLUMN_NAME,       DATA_TYPE,       DATA_LENGTH,       DATA_PRECISION,       DATA_SCALE,       NULLABLE,       DATA_DEFAULT  FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'TABLE' ORDER BY COLUMN_ID

2. Copy the table column name to Excel and use the CONCATENATE function.

It can be used in many cases and organized from the network.