SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two forms. The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES 
(value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...) 
VALUES (value1, value2, value3,...);

Assume we wish to insert a new row in the "Customers" table. We can use the following SQL statement:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) 
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

The CustomerID column is automatically updated with a unique number for each record in the table when you use the INSERT INTO statement. It is also possible to only insert data in specific columns. The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns (and the CustomerID field will of course also be updated automatically):

INSERT INTO Customers (CustomerName, City, Country) 
VALUES ('Cardinal', 'Stavanger', 'Norway');

There is an interesting use of SELECT...INTO that may come in handy, depending on the SQL engine you are using. Look into the use of temporary tables, they can be very handy as you work within a given table. Temporary tables automatically go away when you are finished working with them. Typically, the name of the table will be a special name that the engine will recognize as temporary. For example, with the Microsoft products, if you prefix the name with a pound sign, the engine will automatically know that the table should only be created temporarily.
The SELECT INTO statement selects data from one table and inserts it into a new table.

SQL SELECT INTO Syntax

We can copy all columns into the new table:

SELECT *
 INTO newtable [IN external_database]
 FROM table_n;

Or we can copy only the columns we want into the new table:

SELECT column_name(s)
 INTO newtable [IN external_database]
 FROM table_n;

The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

SQL-2003 introduced CREATE TABLE AS, but Microsoft Access and SQL Server use

SELECT INTO

to create a new table from an existing one:

SELECT columns
INTO new_table
FROM existing_table
[WHERE search_condition];

The SQL standard's version of SELECT INTO is not the same thing. It selects a value into a scalar variable in a host program rather than creating a new table. The Oracle, DB2, and MySQL implementations of SELECT INTO work in the standard way. For portability, you should not use CREATE TABLE AS or SELECT INTO. Instead, create a new, empty table with CREATE TABLE and then populate it with INSERT SELECT.

When you work with information in a database, sometimes it can be helpful to create a copy of the information that fits your criteria in an area that can hold on to it for some time. In addition, there are times when you want simply to 1) copy or 2) move information from one database table to another. In these instances, there is a simple modification to the SELECT statement that you can consider using, which is known as the INTO clause. The really nice thing about the SELECT...INTO statement is that it will automatically create the table for you and will make it match the information that you have selected out of the original data source. Here's the syntax of the SELECT...INTO statement:

SELECT data INTO destination 
FROM source

If you want to create a table that contains just the last name from the customer table, you could use the following statement:

SELECT lastname INTO TempTable 
FROM Customer

The result is a table with a single column, "the Lastname column", populated with the values from the customer table. This is helpful if you want to create a table based on the results of a query of a separate 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.

SELECT *
 INTO newtable [IN external_database]
 FROM table1;

In Oracle, 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.

In its bulk usage

(SELECT ... BULK COLLECT INTO)

, this statement retrieves an entire result set at once.

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

Click the Exercise link below to practice using the INTO clause.
SQL Into Clause - Exercise