Description

The SQL keyword SELECT INTO is used to copy data from an existing table (or multiple tables) 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.

Copy All Data or Table Backup

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

SELECT *
INTO customers_backup2022
FROM customers;

The below SQL 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 SQL 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 SQL 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 SQL 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 SQL 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;

Related Links