Description

The INSERT INTO SELECT statement can be used to copy data from one table and insert it into another table.

  • It requires the matching data types between the source and target tables.
  • The existing records in the target table are unaffected.
  • It can be used to copy data from all the columns or specific columns. In case a value is not specified for a specific column, such a column is filled with a NULL value.

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

Syntax

Here is the basic syntax of the INSERT INTO SELECT 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 in the second syntax below.
INSERT INTO new_table
SELECT * FROM existing_table
WHERE condition;
INSERT INTO new_table (column1, column2, ...)
SELECT column1, column2, ...
FROM existing_table
WHERE condition;

Copy All Data

The below INSERT statement inserts all the data copied from the backup table into an existing table.

INSERT INTO customers
SELECT * FROM customers_backup2022;

Copy Specific Columns

The below INSERT statement inserts all the copied specific column data into another existing table.

INSERT INTO customers (customer_name, address, city, country, postal_code)
SELECT supplier_name, address, city, country, postal_code
FROM suppliers;

Copy Specific Rows

The below INSERT statement copies data from specific rows into another existing table, using the WHERE condition to select rows.

INSERT INTO customers (customer_name, address, city, country, postal_code)
SELECT supplier_name, address, city, country, postal_code
FROM suppliers
WHERE country = 'Germany';

Overall

We now know how to copy data from one table and insert it into another table using the INSERT INTO SELECT statement.

Related Links