How to Use SQL INSERT INTO SELECT to Copy Data Between Tables

Copying rows between tables is a common SQL task. Rather than inserting values one row at a time, INSERT INTO ... SELECT lets you move data using the output of another query.

What Does INSERT INTO SELECT Do?

This statement inserts the results of a SELECT query into an existing table. It works well for migrations, backups, reporting tables, and filtered datasets.

Some useful characteristics include:

Supports filtered inserts.
Can use joins.
Allows value transformations.
Works across popular SQL databases.

Basic Syntax

The statement combines an insert operation with a select query.

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

Example Query

Here's a simple example that stores only IT employees in another table.

INSERT INTO it_staff_backup (original_id, full_name, salary)
SELECT id,
       first_name || ' ' || last_name,
       salary
FROM employees
WHERE department = 'IT';

This query:

Filters employees by department.
Builds a full name.
Copies salary information.
Inserts the results into an existing backup table.

FAQ

Which SQL statement copies table data?

Use INSERT INTO ... SELECT whenever data should be copied into an existing table.

Is INSERT INTO table from SELECT another command?

No. It's another name for the same SQL statement.

Does Oracle support this syntax?

Yes. Oracle implements the same general approach for inserting data selected from another table or query.

How is CREATE TABLE ... SELECT different?

CREATE TABLE ... SELECT creates a new table first. INSERT INTO ... SELECT writes into a table that already exists.

Why work with a visual database client?

Graphical SQL tools make it easier to browse schemas, execute queries, inspect results, and manage databases from one interface.

Conclusion

INSERT INTO ... SELECT is a practical SQL feature for copying and transforming data in one step. Understanding this statement can simplify many everyday database tasks.

Read the full original article here INSERT INTO … SELECT Statement: What You Need to Know.

DEV Community