Paste into request

Question

When you just look at the Oracle INSERT syntax, I noticed that you can insert into a query, for example.

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

Can anyone shed light on what this is for? What can I achieve by inserting into a query that I cannot directly attach to a table?

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

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

instead

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

The same thing, the same implementation plan. It doesn't matter if the query returns a record or not. It:

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

again leads to the same execution plan. Thus, we can assume that it really does not matter what the subquery looks like, as long as we select only the columns from the same table. But no. It:

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

results in "ORA-01779: cannot change a column that maps to a table containing no keys" or "ORA-01732: the data manipulation operation is not legal in this view."

I got the impression that Oracle decided to allow the insertion of a query because they allow you to embed in views, why is the subquery here an ad hoc declaration? Therefore, when you can embed in a view, they, of course, allow you to embed an hoc representation in ad, but no one in their right mind would ever use this syntax, of course :-)

But maybe I'm wrong? Maybe this syntax offers something that I don't know yet? If so tell me :-)

Answer 1

A subquery defines the columns of a table into which rows are to be inserted. Since oracle doc (12c) says:

Specify the column name [...] or columns returned by the subquery into which rows are to be inserted. If you specify a view or a view object, the database inserts rows into the base table of the view.

Example

 create table test_isq ( pk integer not null primary key, data_1 varchar2(40), data_2 varchar2(40), data_3 varchar2(40) ); -- ok insert into (select pk, data_2, data_3 from test_isq) (pk, data_2) values ( 1, 'Test'); insert into (select pk, data_2, data_3 from test_isq) values ( 2, 'Another', 'Test' ); -- fail insert into (select data_1 from test_isq) values ( 'This', 'one', 'fails'); insert into (select data_1 from test_isq) (pk, data_1) values ( 42, 'Nope'); drop table test_isq; 

Answer 2

Inserting into a subquery allows you to limit the results using WITH CHECK OPTION .

For example, let's say you want to allow any department name except for the "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 this bad value is inserted, it causes 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 have 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 with the subquery. I'm not sure why anyone would want to do this, but it is just as easy to put a restriction on the SELECT statement that passes INSERT. And if INSERT uses VALUES, it is trivial to convert it to a SELECT statement.

To see this function, you need to look into the syntax diagrams: insert → single_table_insert → subquery → query_block - → table_reference → query_table_expression → subquery_restriction_clause.

Answer 3

This is because the INSERT statement in SELECT selects data from one table and inserts it into an existing table. Any existing rows in the target table are not affected.