Insert into a query

Question

When just looking up Oracle's INSERT syntax, I noticed you can insert into a query, e.g.

insert into (select * from dept) (deptno, dname) values (99, 'new department');

Can anybody shed some light on what this is meant for? What can I achieve with inserting into a query that I can't with inserting into the table directly?

UPDATE: So far it seems this is just an alternative syntax, so I can write

insert into (select deptno, dname from dept) values (99, 'new department');

instead of

insert into dept (deptno, dname) values (99, 'new department');

Same thing, same execution plan. It doesn't matter whether the query returns a record or not. This:

insert into (select deptno, dname from dept where 1 = 0) values (99, 'new department');

leads again to the same execution plan. So we might assume that it really doesn't matter what the subquery looks like as long as we only select columns from one table. But no. This:

insert into (select deptno, dname from dept cross join some_table)
values (99, 'new department');

leads to "ORA-01779: cannot modify a column which maps to a non key-preserved table" or "ORA-01732: data manipulation operation not legal on this view".

I have the impression that Oracle decided to allow inserting into a query, because they allow inserting into views, for what else is a subquery here then an ad hoc view? So when you can insert into a view, surely they let you insert into an ad hoc view, too, but nobody in their right mind would ever use this syntax, of course :-)

But maybe I am wrong? Maybe this syntax does offer something that I am not yet aware of? If so tell me :-)

Answer 1

Inserting into a subquery allows restricting results using WITH CHECK OPTION.

For example, let's say you want to allow any department name except for "new department". This example using a different value works fine:

SQL> insert into
  2  (select deptno, dname from dept where dname <> 'new department' WITH CHECK OPTION)
  3  values (98, 'old department');

1 row created.

But if that bad value is inserted it throws an error:

SQL> insert into
  2  (select deptno, dname from dept where dname <> 'new department' WITH CHECK OPTION)
  3  values (99, 'new department');
(select deptno, dname from dept where dname <> 'new department' WITH CHECK OPTION)
                           *
ERROR at line 2:
ORA-01402: view WITH CHECK OPTION where-clause violation

I've never seen this feature used in the wild. Views have this option so it makes sense that you should be able to do the same thing with a subquery. I'm not sure why anyone would want to do this though, it's just as easy to put the limit on the SELECT statement that feeds the INSERT. And if the INSERT uses VALUES it's trivial to convert it to a SELECT statement.

You have to really dig into the syntax diagrams to see this feature: insert --> single_table_insert --> subquery --> query_block --> table_reference --> query_table_expression --> subquery_restriction_clause.