SQL INSERT INTO SELECT – Copy Data Between Tables | DbSchema

Table of Contents

1. What SQL INSERT INTO SELECT does
2. Basic syntax
3. Copy all rows into another table
4. Copy selected columns
5. Insert selected data with static values
6. Use INSERT INTO SELECT with JOIN
7. Avoid duplicates when copying data
8. TOP, LIMIT, ORDER BY, and engine differences
9. Use INSERT INTO SELECT safely
10. Run data-copy queries in DbSchema
11. FAQ
12. Conclusion

The SQL INSERT INTO SELECT statement copies data from one query result into another table. It is one of the most useful patterns in SQL for archiving, staging, migrations, reporting tables, and backfilling new columns or tables.

Unlike a plain INSERT INTO ... VALUES, this form pulls rows from an existing table or query. That makes it powerful, but also worth testing carefully when constraints, duplicates, or cross-table joins are involved.

What SQL INSERT INTO SELECT does

Use INSERT INTO SELECT when you want to:

• copy rows into a backup table
• populate a reporting or staging table
• migrate data from an old structure to a new one
• insert only rows that match certain criteria
• combine data from several tables before storing it

It is especially useful when paired with a follow-up cleanup strategy. For example, you might move rows into an archive table with INSERT INTO SELECT, then remove the original rows with SQL DELETE Statement.

Basic syntax

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

The number and order of selected values must match the destination column list.

Copy all rows into another table

If the source and destination tables share the same structure, the simplest version is:

INSERT INTO backup_students
SELECT *
FROM students;

This copies all rows and all columns.

Even when this works, many teams still prefer writing the column list explicitly because it is safer if the schema changes later:

INSERT INTO backup_students (id, name)
SELECT id, name
FROM students;

Copy selected columns

A common real-world use case is copying only some columns into a lighter table:

INSERT INTO student_names (name)
SELECT name
FROM students;

This works even if source and destination column names differ, as long as the selected values line up with the destination columns correctly.

Insert selected data with static values

You can combine selected columns with constant values:

INSERT INTO student_feedback (name, feedback)
SELECT name, 'No Feedback'
FROM students;

This is useful when you want to prefill a new table or assign a default label during the copy.

Use INSERT INTO SELECT with JOIN

High-ranking tutorials almost always include at least one join-based example because that is where INSERT INTO SELECT becomes genuinely useful.

INSERT INTO student_report (name, grade)
SELECT s.name, g.grade
FROM students s
JOIN grades g ON s.id = g.student_id;

This pattern is useful for:

• denormalized reporting tables
• export tables
• snapshot tables used by dashboards

You can also add a filter:

INSERT INTO honors_students (student_id, name, grade)
SELECT s.id, s.name, g.grade
FROM students s
JOIN grades g ON s.id = g.student_id
WHERE g.grade = 'A';

Avoid duplicates when copying data

A major gap in weaker tutorials is duplicate prevention.

If the destination table has a primary key or unique constraint, blindly copying rows can fail. A common pattern is WHERE NOT EXISTS:

INSERT INTO archive_orders (order_id, customer_id, order_total)
SELECT o.order_id, o.customer_id, o.order_total
FROM orders o
WHERE o.order_date < DATE '2024-01-01'
  AND NOT EXISTS (
      SELECT 1
      FROM archive_orders a
      WHERE a.order_id = o.order_id
  );

This makes the insert safer when the archive table may already contain some of the rows.

TOP, LIMIT, ORDER BY, and engine differences

Search results for this topic often include SQL Server and MySQL-specific patterns, so it helps to explain the dialect differences clearly.

SQL Server example

INSERT INTO recent_orders (order_id, order_total)
SELECT TOP (10) order_id, order_total
FROM orders
ORDER BY created_at DESC;

PostgreSQL / MySQL style example

INSERT INTO recent_orders (order_id, order_total)
SELECT order_id, order_total
FROM orders
ORDER BY created_at DESC
LIMIT 10;

ORDER BY matters here. Without it, "top 10" or "first 10" rows may not be deterministic.

Use INSERT INTO SELECT safely

1. Preview the source rows first

Run the SELECT alone before turning it into an insert.

2. Match data types carefully

The source and destination columns should be compatible, even if the names differ.

3. Use transactions for important copy jobs

BEGIN;

INSERT INTO archive_orders (order_id, customer_id, order_total)
SELECT order_id, customer_id, order_total
FROM orders
WHERE order_date < DATE '2024-01-01';

ROLLBACK;

Once you verify the destination rows, replace ROLLBACK with COMMIT.

4. Watch constraints

Primary keys, unique constraints, and foreign keys can all affect whether the insert succeeds. If you need a refresher on keys, review Primary Key in SQL and What Is a Foreign Key?.

Run data-copy queries in DbSchema

DbSchema is useful for INSERT INTO SELECT work because you can inspect the source and destination tables side by side.

You can:

1. connect using a driver such as the PostgreSQL JDBC driver or MySQL JDBC driver
2. run the source query in the SQL Editor
3. inspect the table structure before copying data
4. use schema documentation to verify column meanings and relationships
5. test migration or archive scripts before applying them in production

If you are moving data as part of a broader cleanup workflow, the Data Loader and SQL Editor pages are also worth reviewing.

FAQ

Can I use INSERT INTO SELECT between two databases?

Often yes, if your database engine supports cross-database references and you have permissions.

What happens if the columns do not match?

The statement fails or inserts incorrect data if the order and compatibility are wrong. Always list the destination columns explicitly when possible.

How do I avoid inserting duplicates?

Use patterns such as WHERE NOT EXISTS, staging tables, or engine-specific upsert features when appropriate.

Can I use JOIN in INSERT INTO SELECT?

Yes. Join-based inserts are common for reporting and migration tasks.

Should I use a transaction for INSERT INTO SELECT?

Yes, especially for archive jobs, data migrations, or large copy operations.

Conclusion

The SQL INSERT INTO SELECT statement is one of the most practical ways to move or reshape data inside a database. It becomes even more useful when you combine it with joins, filters, duplicate checks, and transaction-safe testing.

Use DbSchema to preview the source rows, inspect the destination table, and run the final insert with much more confidence than a copy-and-paste workflow in a raw console.