CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Brian
Brian

Reputation: 721

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.

Upvotes: 48

Views: 282698

Answers (5)

cwallenpoole
cwallenpoole

Reputation: 81988

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";

Upvotes: 18

sudarshan
sudarshan

Reputation: 41

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..

Upvotes: 1

sudarshan
sudarshan

Reputation: 41

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 ...

Upvotes: 1

Ryan
Ryan

Reputation: 28177

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.

Upvotes: 112

Chains
Chains

Reputation: 13157

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;

Upvotes: 9

Related Questions