Oracle conditional data insertion

Method 1: declareiExistsint; beginselectcount (*) intoiExistsfrom table where condition; ifiExists0theninsertinto table (...) values (...); endif; end; declare the iExists variable and use the condition to find the number of duplicates in the table. If not, insert the data into the table in Method 2:

Method 1: declare iExists int; begin select count (*) into iExists from table where condition; if iExists = 0 then insert into table (...) values (...); end if; end; declare the iExists variable and use the condition to find the number of duplicates in the table. if not, insert the data into the table in Method 2:

Method 1:

Declare
IExists int;
Begin
Select count (*) into iExists from table where condition;
If iExists = 0 then
Insert into table (...) values (...);
End if;
End;

Declare the iExists variable and find the number of duplicates in the table through the conditions. If no, insert the data into the table.

Method 2:

Merge into target table
Using source table | (select statement)
On (condition)
When matched then update set column = Value
Delete where (column = value)
When not matched then insert (column...) values (value ...);

Use the merge method to update or delete matched data, and insert data that does not match

Oracle does not support the insert into... not exists Method

---------------------------------------------------------------------

MSSQLServer method:

If not exists (select * from table where condition...) insert into table (column...) values (value ...);

Or

Declare @ iExists int
Select @ iExists = COUNT (*) from table where condition ...;
If @ iExists = 0
Begin
Insert into table (column...) values (value ...);
End;

----------------------------------------------------------------------------

Note: The insert sequence is different and the result will be affected (Oracle and MSSQL are different)

1,
Merge into student
Using (select * from dual)
On (student. stno = 1 and (student. stname = 'aaa' or student. stname is null ))
When not matched then
Insert (stno, stname, birth)
Values (1, 'aaa', sysdate );

2,
Merge into student
Using (select * from dual)
On (student. stno = 1 and (student. stname = ''or student. stname is null ))
When not matched then
Insert (stno, stname, birth)
Values (1, '', sysdate );