Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT INTO using Oracle

People also ask

Can we use SELECT into in PL SQL?

The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables.

When we use SELECT into?

The SELECT INTO statement is a query that allows you to create a new table and populate it with the result set of a SELECT statement . To add data to an existing table, see the INSERT INTO statement instead. SELECT INTO can be used when you are combining data from several tables or views into a new table.

When a SELECT into statement does not return a row?

If the SELECT statement returns more than one row, Oracle will raise the TOO_MANY_ROWS exception. If the SELECT statement does not return any row, Oracle will raise the NO_DATA_FOUND exception.

What is the use of SELECT into in SQL?

SELECT... INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it. To view the complete SELECT syntax, see SELECT (Transact-SQL).


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

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;