Use merge statements in Oracle to prevent repeated Inserts

Batch insertion is required in the Project. To prevent duplicate data insertion, you must first determine whether the inserted data already exists. If yes, ignore this insertion; otherwise, insert this data, at the beginning, we first use an SQL statement to judge whether the returned result is true (the record already exists). Ignore this statement; otherwise, this data is inserted, in this case, we need to initiate two connections to the database to insert a piece of data. Later, we found that the efficiency was too low. After Google, we found that the Oracle Database supports the merge statement and conducted a test, if the call succeeds, it will be recorded for future reference by myself and colleagues.

Database: Test

Create Table Test (ID number not null, namevarchar2 (30) not null, sex varchar2 (2) default 'male ')

Insert two pieces of data:

Insert into test values (1, 'sunhenx', 'male') insert into test values (2, 'sunhailong', 'female ')

Merge statement:

Merge into test a using Test B on (. name = B. name) when matched thenupdate set. sex = 'female 'Where. name = 'sunhenx' when not matched then insert values (3, 'sunhenxing', 'femal ')

Note that the update statement and insert statement in the merge statement are a bit different from the general SQL statement format, ^_^.