CODEWITHSUNDEEP
oracle-databaseoracle-apexsql-insertdatabase-trigger
Chris Conaty
Chris Conaty

Reputation: 103

Is it possible to return the Primary Key on an Insert as select statement - Oracle?

So I usually get the Primary Key of a newly inserted record as the following while using a trigger.

insert into table1 (pk1, notes) values (null, "Tester") returning pk1 into v_item;

I am trying to use the same concept but with an insert using a select statement. So for example:

insert into table1 (pk1, notes) select null, description from table2 where pk2 = 2 returning pk1 into v_item;

Note:
1. There is a trigger on table1 which automatically creates a pk1 on insert.
2. I need to use a select insert because of the size of the table that is being inserted into.
3. The insert is basically a copy of the record, so there is only 1 record being inserted at a time.

Let me know if I can provide more information.

Upvotes: 2

Views: 2295

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

You can't use that mechanism; as shown in the documentation railroad diagram:

insert syntax

the returning clause is only allowed with the values version, not with the subquery version.

I'm interpreting your second restriction (about 'table size') as being about the number of columns you would have to handle, possibly as individual variables, rather than about the number of rows - I don't see how that would be relevant here. There are ways to avoid having lots of per-column local variables though; you could select into a row-type variable first:

declare
  v_item number;
  v_row table1%rowtype;
begin
  ...
  select null, description
  into v_row
  from table2 where pk2 = 2;

  insert into table1 values v_row returning pk1 into v_item;

  dbms_output.put_line(v_item);
  ...

or with a loop, which might make things look more complicated than necessary if you really only ever have a single row:

declare
  v_item number;
begin
  ...
  for r in (
    select description
    from table2 where pk2 = 2
  )
  loop
    insert into table1 (notes) values (r.description) returning pk1 into v_item;
    dbms_output.put_line(v_item);
    ...
  end loop;
  ...

or with a collection... as @Dan has posted while I was answering this so I won't repeat! - though again that might be overkill or overly complicated for a single row.

Upvotes: 2

Dan McGhan
Dan McGhan

Reputation: 4659

I don't believe you can do this with insert/select directly. However, you can do it with PL/SQL and FORALL. Given the constraint about the table size, you'll have to balance memory usage with performance using l_limit. Here's an example...

Given this table with 100 rows:

create table t (
  c  number generated by default as identity,
  c2 number
);

insert into t (c2)
select rownum
from dual
connect by rownum <= 100;

You can do this:

declare

  cursor t_cur
  is
    select c2
    from t;

  type t_ntt is table of number;

  l_c2_vals_in t_ntt;
  l_c_vals_out t_ntt;
  l_limit      number := 10;

begin

  open t_cur;

  loop
    fetch t_cur bulk collect into l_c2_vals_in limit l_limit;

    forall i in indices of l_c2_vals_in
    insert into t (c2) values (l_c2_vals_in(i))
    returning c bulk collect into l_c_vals_out;

    -- You have access to the new ids here
    dbms_output.put_line(l_c_vals_out.count);

    exit when l_c2_vals_in.count < l_limit;
  end loop;

  close t_cur;

end;

Upvotes: 3

Related Questions