5 Ways to Upsert in Oracle vs MS SQL – Practical Code Examples

Background

The author, accustomed to MS SQL, needed to work with Oracle for a project and discovered that although basic CRUD looks similar, many details differ, especially when implementing an upsert (insert if not exists, otherwise update).

MS SQL Example

Creating a simple Account table and performing an upsert in MS SQL is straightforward:

if object_id(N'Account',N'U') is not null
    drop table Account;
create table Account (
    AccountID nvarchar(50) primary key not null,
    AccountName nvarchar(50)
);

if not exists (select * from Account where AccountID = '1')
    insert into Account(AccountID,AccountName) values('1','Sam Xiao')
else
    update Account set AccountName = '肖建' where AccountID = '1';

Oracle Table Creation

Oracle lacks the OBJECT_ID function, so the existence of a table is checked via user_tables. The table is then created with PL/SQL:

declare num number;
begin
    select count(1) into num from user_tables where table_name='ACCOUNT';
    if num > 0 then
        execute immediate 'drop table ACCOUNT';
    end if;
    execute immediate 'create table Account (
        AccountID nvarchar2(50) primary key,
        AccountName nvarchar2(50)
    )';
    dbms_output.put_line('成功创建表!');
end;

Oracle Upsert Techniques

Implicit Cursor (SQL%NOTFOUND / SQL%FOUND) Update first; if no rows were affected, insert the new record.

begin
    update account set AccountName = '修改-a' where AccountID = '5';
    if SQL%NOTFOUND then
        insert into account(AccountID,AccountName) values('5','添加-b');
    end if;
end;

Exception Handling (DUP_VAL_ON_INDEX) Attempt an insert; if a primary‑key violation occurs, catch the exception and perform an update.

begin
    insert into account(AccountID,AccountName) values('6','添加-b');
exception
    when DUP_VAL_ON_INDEX then
        update account set AccountName = '修改-b' where AccountID = '6';
end;

Using the Dual Table Query dual to test existence, then branch to insert or update.

declare t_count number;
begin
    select count(*) into t_count from dual where exists (
        select 1 from account where AccountID='11'
    );
    if t_count = 0 then
        dbms_output.put_line('添加');
        insert into account(AccountID,AccountName) values('11','添加-11');
    else
        dbms_output.put_line('修改');
        update account set AccountName = '修改-11' where AccountID = '11';
    end if;
end;

no_data_found Exception Try to select the row; if no_data_found is raised, insert; otherwise update.

declare t_cols number;
begin
    select AccountName into t_cols from account where AccountID = '8';
exception
    when no_data_found then
        insert into account(AccountID,AccountName) values('8','添加-8');
    when others then
        update account set AccountName = '修改-8' where AccountID = '8';
end;

MERGE Statement The MERGE command combines the logic of insert and update in a single statement.

MERGE INTO Account t1
USING (select '3' AccountID, '肖文博' AccountName from dual) t2
ON (t1.AccountID = t2.AccountID)
WHEN MATCHED THEN
    UPDATE SET t1.AccountName = t2.AccountName
WHEN NOT MATCHED THEN
    INSERT (AccountID, AccountName) VALUES (t2.AccountID, t2.AccountName);
COMMIT;

Conclusion

The five methods illustrate the nuances between Oracle and MS SQL for upsert operations, showing that Oracle requires more explicit handling of existence checks and offers several idiomatic approaches.