# Oracle 26ai insert into select returning

In the [21c SQL Language Reference](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/INSERT.html) the following can be found regarding the `INSERT` statement:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771159764198/60306a11-61ad-4b94-a5d6-5681c72f9474.png align="center")

But in the [26ai SQL Language Reference](https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/INSERT.html) that becomes

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771159818740/fb30af08-b7ab-451f-8de9-7073781c5e3c.png align="center")

and that small change opens a world of opportunity, reducing the number of context switches in certain cases, as shown below. But there are also some ceveats!

The classic set-up:

Suppose you have a table with an identity column as primary key and another table with a foreign key pointing to it. Further, suppose you need PL/SQL to insert records in both that parent and child table. In that case, you’ll need to to use `insert … returning` in orer to get the PK-value created during that insert in order to use it for the subsequent insert(s) into the child table.

Before 26ai, the `retunring_clause` was only possible when using the `values_clause`. If you wanted to use a subquery (an “insert-select”), there was no way to also have a `retunring` clause.

For example, when using *collections* in Oracle Apex, it is common to use PL/SQL to select from `apex_collections` to get the data stored in collections into records in tables.

Before 26ai, this required

1. select …  
    from apex\_collection  
    into :plsql\_variables\_or\_records  
    where …
    
2. insert into destination\_parent\_table (…)  
    values (:plsql\_variables\_or\_records, …)  
    returning new ID as :variable\_for\_parent\_key
    
3. insert into destination\_child\_table (…)  
    values (:variable\_for\_parent\_key, …)
    

So, that are 3 SQL calls in PL/SQL and therefore also 3 context switches…

In 26ai this can be reduced to

1. insert into destination\_parent\_table (…) values (  
    select … from apex\_collection where …  
    ) returning new ID as :variable\_for\_parent\_key
    
2. insert into destination\_child\_table (…)  
    select :variable\_for\_parent\_key, …  
    from apex\_collections  
    where …
    

So that is

* 1 query less
    
* 1 context switch less
    
* less data in PL/SQL => less memory usage
    

Some code to illustrate this and expose some issues:

```sql
drop table if exists src purge;
create table src (id number, val varchar2(30));

-- 1) Multi record insert
   -- Very nice for creating test data or (initial) data loading.
   -- Probably less usefull in application development.
   -- Here it is used to populate a source table (to select from)
insert into src (id, val) values
   (1, 'One'),
   (2, 'Two'),
   (3, 'Three');
commit;

/*
The new way of using subqueries when inserting:
There has always been
   insert into dest (col1, col2)
   select col_a, col_b from src;
It was previously not possible to add a returning clause onto this,
probably since that select potentially returns multiple records.
That fact results in having to do 2 sql-calls, in PL/SQL:
   1 to select the values into variables
   2 to do the insert using the "insert values"-way or working:
         select col_a, col_b
            into :param1, :param2
            from src
            where id = 1;
         insert into dest (col1, col2)
            values (:param1, :param2)
            returning id into :id;
But now, the values(...) can contain a select
*/

-- Testcase for the new functionality:
drop table if exists dest purge;
create table dest (id number GENERATED ALWAYS AS IDENTITY, src_id number, val varchar2(30));

insert into dest (src_id, val) values (
   select id, val from src where id = 1
);
-- Note that it has to be a single row query (or "ORA-01427: single-row subquery returns more than one row" is raised)
-- If you want to insert multiple rows (implying you don't need "returning") just use the old style insert-select.
-- This really only adds value when using "returning into":

set serveroutput on
declare
   dst_id number;
begin
   insert into dest (src_id, val) values (
      select id, val from src where id = 3
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);

   insert into dest (src_id, val) values (
      select id, val from src where id = 2
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);

   insert into dest (src_id, val) values (
      select id, val from src where id = 1
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);

   insert into dest (src_id, val) values (
      select id, val from src where id = 42 --does not exist
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);
end;
/

select * from dest;
```

The caveat is that this returns

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1771162249013/2758f6ae-e42b-43c6-8ebd-556d74a8a036.png align="center")

So, also when the subquery returns no records, the insert is performed without raising errors…

(Note that in the PL/SQL sample above, the returned PK-value is not used to insert a child record. This part has been intentionally left out since it is not relevent to the new 26ai functionality of the `insert` statement.)