Into Statement  «Prev 

SQLINTO statement

Inserting Results into an existing table

If you need to put information from your table into an existing table, you will need to use the INSERT...INTO statement. INSERT...INTO assumes that a table exists (and therefore will also not overwrite any existing information) and simply copies information from the source table to the destination.

SQL SELECT INTO Syntax


SELECT *
 INTO newtable [IN Externaldatabase]
 FROM table1;

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections.
Default Usage
In its default usage (SELECT ... INTO), this statement retrieves one or more columns from a single row.

Bulk Usage of Select INTO

In its bulk usage 
(SELECT ... BULK COLLECT INTO)

, this statement retrieves an entire result set at once.

The following example demonstrates the use of the SELECT-INTO statement.


DECLARE
  deptid        employees.department_id%TYPE;
  jobid         employees.job_id%TYPE;
  emp_rec       employees%ROWTYPE;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  all_emps      emp_tab;
BEGIN
  SELECT department_id, job_id INTO deptid, jobid 
     FROM employees WHERE employee_id = 140;
  IF SQL%FOUND THEN 
    DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid || ', Job Id: ' || jobid);
  END IF;
  SELECT * INTO emp_rec FROM employees WHERE employee_id = 105;
  SELECT * BULK COLLECT INTO all_emps FROM employees;
  DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT);
END;
/