How to Correctly Use INSERT SELECT in Oracle 11g to Avoid ORA-00936?

Inserting Data via SELECT Query in Oracle 11G

In Oracle 11G, the INSERT SELECT statement allows efficient data transfer from one table to another using a subquery. However, users may encounter unexpected errors if their syntax is not correct.

User Query:

A user attempted to insert the results of a subquery into a new table using the following statement:

insert into table1 (col1, col2) values (select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2);

However, the user encountered the following error:

SQL Error: ORA-00936: missing expression

Solution:

The provided syntax is incorrect. The INSERT SELECT statement in Oracle does not require the VALUES keyword, which is commonly used in other SQL dialects. The correct syntax should be:

insert into table1 (col1, col2)
select t1.col1, t2.col2
from oldtable1 t1, oldtable2 t2

In other words, simply remove the VALUES keyword from the beginning of the statement. This will enable Oracle to correctly execute the INSERT SELECT query, inserting the results of the subquery into table1.

The above is the detailed content of How to Correctly Use INSERT SELECT in Oracle 11g to Avoid ORA-00936?. For more information, please follow other related articles on the PHP Chinese website!