SQL SELECT INTO Statement

The SELECT INTO statement is used to create a new table from an existing one by copying the existing table's columns and data.

When creating a table using the SELECT INTO statement, the new table will be populated with data from the existing table.

Syntax

The syntax for the SELECT INTO statement can be given as follows:

• To copy all columns into a new table, use the following syntax:

  SELECT *
  INTO new_table_name [ IN external_database ]
  FROM old_table_name
  WHERE condition
  

• To copy only some columns into a new table, use the following syntax:

  SELECT column1, column2, ... columnN
  FROM new_table_name [ IN external_database ]
  FROM old_table_name
  WHERE condition;
  

Parameters:

• colmun1, column2, ...columnN: The name of columns that you want to select.
• new_table_name: The name of the new table that will be created with the SELECT INTO statement (new_table_name must not already exist).
• old_table_name: The name of the existing table you want to retrieve data from.
• IN external_database: Optional. If you want to create the new table in another database.

Demo Table

Let us suppose that we have a table called "Employee" with the following data:

SQL SELECT INTO Examples

The following SQL statement will create a new backup copy of the "Employee" table:

SELECT * 
INTO employee_backup_01
FROM employee;

If you want to copy a table into a new table in another database, you can use the IN clause with the SELECT INTO statement as follows:

SELECT * 
INTO employee_backup_01 IN 'another_database.db'
FROM employee;

The following SQL statement will copy just a few columns (first_name, last_name, salary) of the "Employee" table:

SELECT first_name, last_name, salary 
INTO employee_backup_01
FROM employee;

The following SQL statement will copy only employees whose working in the "Development" department:

SELECT *
INTO employee_development_dept
FROM employee
WHERE dept_name = 'Development';

The following SQL statement will create a new empty table using the structure of an already existing table:

SELECT *
INTO employee_empty
FROM employee
WHERE 2 = 3;

Note: The SELECT INTO statement can also be used to create a new empty table using the structure of an already existing table by adding a WHERE with the condition that will always be FALSE to return no data.