Description

The SELECT INTO statement copies data from an existing table to a new table.

  • It can be used to copy all or only specific columns from an existing table to a new table.
  • Columns names can be retained as is or renamed while copying.
  • The new table can be created on the same or another database.

Let's look at its syntax and example for more details.

Syntax

Here is the basic syntax of the SELECT INTO statement.

  • Use SELECT * to copy all the columns from an existing table to a new table.
  • If we need to copy only specific columns, then provide columns as mentioned.
  • Optional, but if we need column names on the new table to be different, then use the AS keyword to provide column aliases as mentioned.
  • Optional, but if we need to create the new table on a different database, then use the IN keyword to provide the database name as mentioned.
SELECT column1 [AS new_table_column1], column2 [AS new_table_column2], ...
INTO new_table [IN external_db]
FROM table
WHERE condition;

Copy All Data or Table Backup

The below SELECT statement takes a backup of the table data into another table on the same database.

SELECT *
INTO customers_backup2022
FROM customers;

The below SELECT statement takes a backup of the table data into another table on another database.

SELECT *
INTO customers_backup2022 IN 'backup_db'
FROM customers;

Copy Specific Columns

The below SELECT statement copies all the data from specific columns into another table on the same database.

SELECT customer_id, customer_name, hire_date, salary
INTO customers_backup2020
FROM customers;

Copy Specific Rows

The below SELECT statement copies all the data from specific rows into another table on the same database, using the WHERE condition to select rows.

SELECT *
INTO customers_backup2020
FROM customers
WHERE country = 'France';

Copy Data From Multiple Tables

The below SELECT statement copies data from two tables into a new table on the dame database, using the LEFT JOIN to generate the required data.

SELECT customers.customer_name, orders.order_id, orders.order_date, orders.order_value
INTO customers_orders_backup2020
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Create a Similar Empty Table

The below SELECT statement copies data from two tables into a new table on the dame database, using the WHERE condition that selects no rows.

  • This creates a new table with the same structure as that of the existing table, but no rows are copied.
SELECT *
INTO customers_new
FROM customers
WHERE 1 = 0;

Overall

We now know how to create a new table and copy data from existing tables with s single statement using SELECT INTO statement.

Related Links