47

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.

Brian
  • 711
  • 1
  • 7
  • 14
  • The Oracle documentation is comprehensive and online. You can find the syntax for INSERT statements in the SQL Reference. Check it out: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9014.htm#i2126076 – APC Sep 06 '11 at 17:26
  • See http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from for database agnostic solution. – Vadzim Aug 21 '14 at 10:17

5 Answers5

107

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.

Ryan
  • 26,884
  • 9
  • 56
  • 83
18

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";
cwallenpoole
  • 79,954
  • 26
  • 128
  • 166
9

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;
Chains
  • 12,541
  • 8
  • 45
  • 62
1

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

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
sudarshan
  • 41
  • 2
1

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

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
sudarshan
  • 41
  • 2