INSERT SELECT statement in Oracle 11G

sql oracle oracle11g

Brian · Sep 6, 2011 · Viewed 213.3k times · Source

I'm trying to run a very simple sql statement in Oracle 11g.

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

Very simple query. Cartesian join old table 1 to old table 2, put the resulting values into table 1.

I've run the subquery by itself, and it works perfectly.

 select t1.col1, t2.col2 from oldtable1 t1, oldtable2 t2

When I try to run the full statement, I get the following error:

 SQL Error: ORA-00936: missing expression
 00936. 00000 -  "missing expression"

I can't get it to work in MySql either. Something is wrong with my statement, but I'm not sure what it is.

Answer

Your query should be:

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

I.e. without the VALUES part.