Tutorials

SQL INSERT INTO SELECT: Syntax, Examples, and Safe Data Copy

Learn SQL INSERT INTO SELECT with JOINs, filters, duplicate prevention, and transaction-safe data copy patterns for MySQL, PostgreSQL, and SQL Server.

by Malik Umair Sajjad · 12-Oct-2023 · Updated 10-Aug-2026

On this page

DbSchema showing the Sakila sample schema, with source and destination tables and their foreign keys side by side

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.

INSERT INTO SELECT vs SELECT INTO

A common point of confusion is how INSERT INTO SELECT differs from SELECT INTO. The rule is simple: INSERT INTO SELECT writes rows into a target table that already exists, while SELECT INTO creates a brand new target table on the fly. If you want to create a new table from a query result, read our complete guide to the SQL SELECT INTO Statement.

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

Joining the source tables before the insert is where INSERT INTO SELECT earns its place, because the destination row can combine columns that live in different tables.

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

Duplicate prevention is the step most copy jobs get wrong.

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

Limiting the number of copied rows is dialect-specific, so check the syntax your engine expects before running the insert.

Database	Common way to limit inserted rows
SQL Server	`TOP (n)`
MySQL / PostgreSQL	`LIMIT n` in the source SELECT
Oracle	`FETCH FIRST n ROWS ONLY`

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?.

Copy data between two databases

Often you need to move data between different databases on the same server. You can do this easily if your database engine supports cross-database references and your user account has the required permissions.

INSERT INTO ReportingDB.dbo.monthly_sales (region, total)
SELECT region, total
FROM LiveDB.dbo.sales
WHERE month = '2024-01';

The exact naming convention depends on your SQL dialect (for example, using database.schema.table in SQL Server, or referencing schemas in PostgreSQL), but the core syntax remains identical.