INSERT SELECT statement in Oracle 11G

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.

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.

Get rid of the values keyword and the parens. You can see an example here.

This is basic INSERT syntax:

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);

This is the INSERT SELECT syntax:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";

You don't need the 'values' clause when using a 'select' as your source.

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

for inserting data into table you can write

insert into tablename values(column_name1,column_name2,column_name3);

but write the column_name in the sequence as per sequence in table ...

There is an another option to insert data into table ..

insert into tablename values(&column_name1,&column_name2,&column_name3);

it will open another window for inserting the data value..