Using the INSERT INTO SELECT Statement to Insert Data from One Table into Another

INSERT INTO SELECT statement allows you to insert data from one table into another table. This statement is useful when you want to copy data from an existing table or retrieve data from a complex query and insert it into another table.

Table of Contents

Basic Syntax

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

target_table: Specifies the name of the table where data will be inserted.
column1, column2, …: Specifies the columns in the target table where data will be inserted.
source_table: Specifies the name of the table or tables, or the result of a query, from which data will be selected.
condition: Specifies optional conditions to filter the data being selected.

Inserting All Columns

If you want to insert all columns from the source table into the target table, you can omit the column list in the INSERT INTO statement.

INSERT INTO target_table
SELECT *
FROM source_table;

All columns from the “source_table” will be inserted into the “target_table”.

Make sure that the column names and data types in the source table match the corresponding columns in the target table.

Inserting Specific Columns

To insert specific columns from the source table into the target table, you need to specify the column list in the INSERT INTO statement.

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table;

Only the specified columns from the “source_table” will be inserted into the corresponding columns in the “target_table”.

Filtering Data

You can use the SELECT statement within the INSERT INTO SELECT statement to filter the data being inserted.

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
WHERE condition;

The data selected from the “source_table” will be filtered based on the specified condition before being inserted into the “target_table”.

With INSERT INTO SELECT statement, you can insert data from one table into another, either copying all columns or specific columns and filtering the data as needed. This statement provides a powerful way to populate or synchronize data between tables and perform complex data manipulation tasks in your SQL database.