I'm trying to do a SELECT INTO using Oracle. My query is:
SELECT * INTO new_table FROM old_table;
But I get the following error:
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
Any ideas what's wrong?
The Standard behavior of the above should be as I originally thought: However Oracle implemented it totally differently in their own dialect of SQL Oracle Docs on Insert ... Select
select into
to create a new table is not part of the standard. The SQL standard to create a table based on a select is create table .. as select ...
. In the SQL standard SELECT INTO
is defined to read a column value into a variable in a programming language
If NEW_TABLE already exists then ...
insert into new_table
select * from old_table
/
If you want to create NEW_TABLE based on the records in OLD_TABLE ...
create table new_table as
select * from old_table
/
If the purpose is to create a new but empty table then use a WHERE clause with a condition which can never be true:
create table new_table as
select * from old_table
where 1 = 2
/
Remember that CREATE TABLE ... AS SELECT creates only a table with the same projection as the source table. The new table does not have any constraints, triggers or indexes which the original table might have. Those still have to be added manually (if they are required).
select into
is used in pl/sql to set a variable to field values. Instead, use
create table new_table as select * from old_table
select into
is part of pl/sql. It is a language for writing stored procedures and has no direct relation to sql standard. And yes, Oracle made many things that were never part of the standard =)
SELECT INTO
is not standard SQL. The standard only defines create table .. as select ..
Use:
create table new_table_name
as
select column_name,[more columns] from Existed_table;
Example:
create table dept
as
select empno, ename from emp;
If the table already exists:
insert into new_tablename select columns_list from Existed_table;
Try using this below statement instead of using select into in oracle:
select * from(select * from table1) table2 ;
Success story sharing