SQL INSERT INTO SELECT: Syntax, Examples, and Safe Data Copy
For someone who can write a SELECT and now has to copy its rows into a table that already exists.
On this page
INSERT INTO SELECT copies the rows of a query result into a table that already exists. For example, you can copy the customers in France from a customers table into an archive table.
Without INSERT INTO SELECT, your application reads the rows and writes them back one at a time. With it, the database copies them in one statement, and they never leave the server.
The statement is an INSERT that takes its rows from a SELECT:
INSERT INTO destination_table (column1, column2, ...)
SELECT expression1, expression2, ...
FROM source_table
WHERE condition;
destination_tablereceives the rows. It has to exist before the statement runs.(column1, column2, ...)lists the destination columns to fill. A column left out of the list gets its default value, or NULL.SELECTis any query. It can filter, join, group, and compute. Each row of its result becomes a new row of destination_table.- Values go into the listed columns by position, so the first SELECT value fills the first listed column. The names don't have to match.
Create two sample tables: customers with four rows, and an empty customers_archive:
CREATE TABLE customers (
customer_id int PRIMARY KEY,
name varchar(50),
country varchar(50)
);
INSERT INTO customers VALUES
(1, 'Ana', 'France'),
(2, 'Ben', 'Germany'),
(3, 'Carla', 'France'),
(4, 'Dan', 'Spain');
CREATE TABLE customers_archive (
customer_id int PRIMARY KEY,
name varchar(50),
country varchar(50),
archived_on date
);
customer_id is the primary key of both tables: no two rows of one table can share a customer_id.
Run the examples in order. Most of them add rows to customers_archive.
Every result below is the same on PostgreSQL 18, MySQL 9.1, and SQLite 3.50, unless an engine is named. The SQL Server statements were checked against Microsoft's documentation only.
Practice this in Lesson 13 of the SQL tutorial: you run INSERT, UPDATE and DELETE on a shop database in your browser, and each exercise is checked.
INSERT INTO SELECT
The basic INSERT INTO SELECT copies every row of the SELECT result. For example, you can archive the customers in France. To move rows instead, copy them first, then remove the originals with DELETE.
INSERT INTO customers_archive (customer_id, name, country)
SELECT customer_id, name, country
FROM customers
WHERE country = 'France';
SELECT * FROM customers_archive now returns:
| customer_id | name | country | archived_on |
|---|---|---|---|
| 1 | Ana | France | NULL |
| 3 | Carla | France | NULL |
Ana and Carla are the customers in France, so the INSERT added two rows. Each value went into the column at the same position in the column list. The column list leaves out archived_on, so archived_on is NULL in both rows.
Constant values
A constant in the SELECT list puts one fixed value into every copied row. For example, you can copy Ben with the date 2026-11-11 in archived_on, a column that customers lacks.
INSERT INTO customers_archive (customer_id, name, country, archived_on)
SELECT customer_id, name, country, '2026-11-11'
FROM customers
WHERE country = 'Germany';
The archive now holds:
| customer_id | name | country | archived_on |
|---|---|---|---|
| 1 | Ana | France | NULL |
| 2 | Ben | Germany | 2026-11-11 |
| 3 | Carla | France | NULL |
Ben is the only customer in Germany, so the INSERT added one row. The date went into archived_on, because both are fourth in their lists.
INSERT INTO SELECT with JOIN
A SELECT with a JOIN combines columns from two tables into each new row. For example, you can copy each order with its customer's name into a report table. Create an orders table and an empty order_report table:
CREATE TABLE orders (
order_id int,
customer_id int
);
INSERT INTO orders VALUES (10, 1), (11, 3), (12, 3);
CREATE TABLE order_report (
order_id int,
name varchar(50)
);
INSERT INTO order_report (order_id, name)
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;
The report now holds:
| order_id | name |
|---|---|
| 10 | Ana |
| 11 | Carla |
| 12 | Carla |
Each order matched one customer, and the name came from the customers table. Carla appears twice, because she has two orders and a join returns one row for each match. Ben and Dan have no orders, so the join left them out.
NOT EXISTS for rows already copied
NOT EXISTS makes the SELECT return only the rows that are not in the destination yet. For example, you can rerun the same copy without copying a customer twice.
INSERT INTO customers_archive (customer_id, name, country)
SELECT c.customer_id, c.name, c.country
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM customers_archive a
WHERE a.customer_id = c.customer_id
);
The archive now holds:
| customer_id | name | country | archived_on |
|---|---|---|---|
| 1 | Ana | France | NULL |
| 2 | Ben | Germany | 2026-11-11 |
| 3 | Carla | France | NULL |
| 4 | Dan | Spain | NULL |
For each customer, the subquery inside NOT EXISTS looks for the same customer_id in the archive. Ana, Ben, and Carla were there already, so the INSERT copied only Dan. A second run of the same INSERT copies nothing.
PostgreSQL, SQLite, and MySQL can also skip such rows with a clause of their own. PostgreSQL and SQLite add ON CONFLICT (customer_id) DO NOTHING after the SELECT. MySQL writes INSERT IGNORE INTO.
On SQLite, add WHERE true to a SELECT that has no WHERE. SQLite reads ON CONFLICT only after a SELECT with a WHERE.
MySQL's IGNORE also turns other errors into warnings. If a value doesn't fit its column, MySQL stores an adjusted value instead.
INSERT INTO SELECT and SELECT INTO
On PostgreSQL, MySQL, and SQLite, CREATE TABLE AS creates a new table and fills it from a query. For example, you can create a table of the customers in France:
CREATE TABLE french_customers AS
SELECT customer_id, name
FROM customers
WHERE country = 'France';
SELECT * FROM french_customers returns:
| customer_id | name |
|---|---|
| 1 | Ana |
| 3 | Carla |
The new table took its two columns and its two rows from the query. On all three engines, french_customers has no primary key, although customer_id is the primary key of customers. If you need the primary key, create the table yourself and fill it with INSERT INTO SELECT.
On SQL Server, write this copy as SELECT INTO. SELECT INTO also creates the table, without any index or constraint of the source table:
SELECT customer_id, name
INTO french_customers
FROM customers
WHERE country = 'France';
PostgreSQL accepts SELECT INTO as well. In MySQL, SELECT INTO writes into variables or a file, so MySQL rejects this statement with "Undeclared variable: french_customers".
SQL SELECT INTO Statement explains SELECT INTO in more detail.
ORDER BY with LIMIT or TOP
LIMIT keeps only the first rows of the sorted SELECT result, and the INSERT copies only those. For example, you can copy only the customer with the highest ID, or a few rows to test with. Add a fifth customer first:
INSERT INTO customers VALUES (5, 'Eva', 'Italy');
INSERT INTO customers_archive (customer_id, name, country)
SELECT customer_id, name, country
FROM customers
ORDER BY customer_id DESC
LIMIT 1;
The archive now holds:
| customer_id | name | country | archived_on |
|---|---|---|---|
| 1 | Ana | France | NULL |
| 2 | Ben | Germany | 2026-11-11 |
| 3 | Carla | France | NULL |
| 4 | Dan | Spain | NULL |
| 5 | Eva | Italy | NULL |
ORDER BY sorted the customers from the highest ID down, and LIMIT 1 kept the first one, Eva. The earlier INSERTs copied the other four rows.
SQL Server has no LIMIT. There, write TOP right after SELECT:
INSERT INTO customers_archive (customer_id, name, country)
SELECT TOP (1) customer_id, name, country
FROM customers
ORDER BY customer_id DESC;
In an INSERT INTO SELECT, ORDER BY only decides which rows LIMIT or TOP keeps. Microsoft's documentation adds that in such a statement, ORDER BY "doesn't guarantee the rows are inserted in the specified order". Without ORDER BY, LIMIT keeps an unpredictable subset of the rows.
Copy between two databases
In MySQL, a table name can start with its database name, so one INSERT INTO SELECT can read from one database and write into another. For example, you can copy the customers in France into a second database.
This copy runs on MySQL only. Put the sample tables in a database named shop. Create a second database with a table for the copy:
CREATE DATABASE reporting;
CREATE TABLE reporting.customer_names (
customer_id int,
name varchar(50)
);
INSERT INTO reporting.customer_names (customer_id, name)
SELECT customer_id, name
FROM shop.customers
WHERE country = 'France';
SELECT * FROM reporting.customer_names returns:
| customer_id | name |
|---|---|
| 1 | Ana |
| 3 | Carla |
The INSERT names reporting, and the SELECT names shop, so the two rows went from shop into reporting, on the same server.
SQL Server's INSERT accepts names of the form database.schema.table, such as reporting.dbo.customer_names.
In PostgreSQL, one connection reaches one database. To copy between two PostgreSQL databases, postgres_fdw makes a table of the other database visible as a foreign table. The SELECT then reads from that foreign table.
What goes wrong
Before each copy, run its SELECT alone and check the rows. The INSERT copies exactly those rows.
Empty the archive for the examples below:
DELETE FROM customers_archive;
Columns in the wrong order
The SELECT below lists country before name:
INSERT INTO customers_archive (customer_id, name, country)
SELECT customer_id, country, name
FROM customers
WHERE country = 'France';
The archive now holds:
| customer_id | name | country | archived_on |
|---|---|---|---|
| 1 | France | Ana | NULL |
| 3 | France | Carla | NULL |
All three engines accepted the INSERT, because name and country both hold text. Values go in by position, so each country went into name. Compare the SELECT list with the column list before you run the copy.
A duplicate key rejects the whole statement
The archive now holds customers 1 and 3. Copy every customer into the archive:
INSERT INTO customers_archive (customer_id, name, country)
SELECT customer_id, name, country
FROM customers;
PostgreSQL rejects the INSERT:
ERROR: duplicate key value violates unique constraint "customers_archive_pkey"
DETAIL: Key (customer_id)=(1) already exists.
MySQL reports "Duplicate entry '1' for key 'customers_archive.PRIMARY'". SQLite reports "UNIQUE constraint failed: customers_archive.customer_id".
On all three engines, the whole INSERT fails, and the archive keeps its two rows. Ben, Dan, and Eva aren't copied, although their IDs are new. SQL Server also stops the statement and loads no rows.
To avoid the error, add the NOT EXISTS condition shown earlier.
SELECT * without a column list
The customers table has three columns, and customers_archive has four. Copy with SELECT * and no column list:
INSERT INTO customers_archive
SELECT * FROM customers WHERE country = 'Spain';
MySQL rejects the INSERT with "Column count doesn't match value count at row 1". SQLite rejects it with "table customers_archive has 4 columns but 3 values were supplied". PostgreSQL fills archived_on, the column without a value, with its default, and copies Dan.
Write the column list. Without it, the copy depends on the column order of both tables, on every engine. With it, a column added to either table later leaves the copy unchanged.
Run the copy in DbSchema
The SQL Editor in DbSchema runs the SELECT and the INSERT against the connected database. The SQL Editor holds the INSERT in an open transaction: a group of changes that you keep with Commit or undo with Rollback.
- Press Editor in the toolbar and choose SQL Editor.
- Type the SELECT alone, press Run, and read its rows in the result grid.
- Add the INSERT INTO line above the SELECT, and press Run again.
- Press Commit to keep the new rows, or Rollback to undo them.
Run writes the new rows into the connected database and leaves the DbSchema model file unchanged. Commit makes the rows permanent.
Download DbSchema at https://dbschema.com/download.html and connect it to PostgreSQL, MySQL, or SQLite. Run the sample statements with Run Script in the SQL Editor, then run each copy and read the archive after each one. The free Community Edition includes the database connection and the SQL Editor with its Commit and Rollback buttons.

