How to Insert Rows from a SELECT in SQL

Q: How do I insert the results of a SELECT into an existing table?

Write the query where the VALUES list would go: INSERT INTO target (a, b) SELECT x, y FROM source WHERE ...;. Leave out VALUES and the parentheses around the query; INSERT INTO b (id, name) VALUES (SELECT id, name FROM a) is a syntax error (SQLite reports near "SELECT": syntax error). This form works in SQLite, PostgreSQL, MySQL and SQL Server.

Q: What is the difference between SELECT INTO and INSERT INTO SELECT?

INSERT INTO t SELECT ... adds rows to a table that already exists and keeps its keys and constraints. SELECT ... INTO t FROM ... (SQL Server, PostgreSQL) creates a new table t from the query and fails if t already exists. In MySQL, SELECT ... INTO only assigns to variables, so SELECT id, customer INTO big2 FROM orders fails with Undeclared variable: big2; use CREATE TABLE big2 AS SELECT ... there.

Q: How do I use ON CONFLICT with INSERT … SELECT in SQLite?

Add a WHERE clause to the SELECT, even WHERE true: INSERT INTO customers (email, name) SELECT email, name FROM signups WHERE true ON CONFLICT (email) DO NOTHING;. Without it, SQLite reads ON as the start of a join condition and fails with near "DO": syntax error.

Q: How do I copy a table's structure without its rows?

Use a query that returns no rows: CREATE TABLE empty_copy AS SELECT * FROM orders WHERE false; creates the columns with zero rows, but without the primary key or other constraints. To keep the keys, use CREATE TABLE t LIKE s in MySQL or CREATE TABLE t (LIKE s INCLUDING ALL) in PostgreSQL.

Q: How do I swap the values of two columns?

Assign both in one UPDATE: UPDATE names SET first = last, last = first;. In SQLite, PostgreSQL and SQL Server every right-hand side reads the row as it was before the update, so a row (1, 2) becomes (2, 1). MySQL applies the assignments left to right, so the same statement turns (1, 2) into (2, 2); there you need a temporary column or variable.

Quick answer

Put a SELECT where the VALUES list would go: INSERT INTO target (a, b) SELECT x, y FROM source WHERE …;. Every row the query returns is inserted, matched to the column list by position. To create a new table from the result instead, use CREATE TABLE new_table AS SELECT … (or SELECT … INTO new_table in SQL Server).

INSERT takes its rows from one of two places: a VALUES list you type out, or a query. With a query there is no VALUES keyword and no parentheses around the SELECT; the statement copies every row the query returns, so a WHERE, a join or an aggregate decides what gets copied. The examples below copy rows into an existing table, add fixed values on the way, skip rows that are already there, create a new table from a query, and copy one column into another inside the same table. They run on SQLite; section 6 gives the PostgreSQL, MySQL and SQL Server forms. Hit Run on any of them, then change the query and run it again.

1INSERT INTO … SELECT: copy rows into an existing tableRecommended

Name the target table and its columns, then write the query that produces the rows. The WHERE clause picks which rows are copied: here, only the shipped orders. changes() then reports how many rows the insert added.

insert-select.sql

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    total REAL NOT NULL,
    status TEXT NOT NULL
);
CREATE TABLE shipped_orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    total REAL NOT NULL
);
INSERT INTO orders (id, customer, total, status) VALUES
    (1, 'Ana', 40.0, 'shipped'),
    (2, 'Ben', 15.5, 'open'),
    (3, 'Cleo', 99.9, 'shipped'),
    (4, 'Dev', 12.0, 'shipped'),
    (5, 'Eli', 60.0, 'open');

-- No VALUES keyword: the SELECT supplies the rows
INSERT INTO shipped_orders (id, customer, total)
SELECT id, customer, total
FROM orders
WHERE status = 'shipped';

SELECT changes() AS inserted;
SELECT * FROM shipped_orders;

Output

changes() reports 3 inserted rows, and the new table holds orders 1, 3 and 4. Always list the target columns: without the list, the SELECT has to return a value for every column in the table's declared order, and adding a column to the table later breaks the statement. The query can be anything that returns rows, including a join, a GROUP BY or a UNION.

2Mix columns, expressions and fixed values

The select list doesn't have to be bare column names. A literal such as 'draft' puts the same value in every inserted row, and an expression computes a value from the source row. This is how you fill a column the source table doesn't have, such as a status or a fixed date.

insert-literals.sql

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total REAL);
CREATE TABLE invoices (
    order_id INTEGER,
    amount REAL,
    status TEXT,
    issued TEXT
);
INSERT INTO orders (id, customer, total) VALUES
    (1, 'Ana', 40.0),
    (2, 'Ben', 15.5),
    (3, 'Cleo', 99.9);

-- Columns, expressions and fixed literals in one SELECT.
-- Values match the column list by position, not by name.
INSERT INTO invoices (order_id, amount, status, issued)
SELECT id, round(total * 1.2, 2), 'draft', '2026-03-14'
FROM orders
WHERE total > 20;

SELECT * FROM invoices;

Output

Both invoices get draft and 2026-03-14, and the amounts are the order totals plus 20% (48.0 and 119.88). The column names in the SELECT are ignored: the first value goes into order_id, the second into amount, and so on. If the counts differ, SQLite rejects the statement with 2 values for 3 columns (for two values and three listed columns).

3Insert only the rows that aren't there yet

Copying into a table that already has some of the rows fails on the first duplicate key, here with UNIQUE constraint failed: customers.email. Add a WHERE NOT EXISTS that looks for the row in the target, and only the missing rows are copied. The statement is safe to run again: the second run finds every row already there and inserts nothing.

insert-missing.sql

CREATE TABLE signups (email TEXT, name TEXT);
CREATE TABLE customers (email TEXT PRIMARY KEY, name TEXT);
INSERT INTO customers (email, name) VALUES
    ('[email protected]', 'Ana'),
    ('[email protected]', 'Ben');
INSERT INTO signups (email, name) VALUES
    ('[email protected]', 'Ben'),
    ('[email protected]', 'Cleo'),
    ('[email protected]', 'Dev');

-- Copy only the signups that aren't customers yet
INSERT INTO customers (email, name)
SELECT s.email, s.name
FROM signups AS s
WHERE NOT EXISTS (
    SELECT 1 FROM customers AS c WHERE c.email = s.email
);
SELECT changes() AS added;

-- Running it again adds nothing
INSERT INTO customers (email, name)
SELECT s.email, s.name
FROM signups AS s
WHERE NOT EXISTS (
    SELECT 1 FROM customers AS c WHERE c.email = s.email
);
SELECT changes() AS added;

SELECT * FROM customers ORDER BY email;

Output

The first insert adds 2 rows (Cleo and Dev; Ben is already a customer), the second adds 0, and the table ends with four customers. NOT EXISTS works the same in every database. If you also want to update the rows that do exist, or to let the unique key do the check with ON CONFLICT, see insert or update if a row exists.

4CREATE TABLE … AS SELECT: create and fill a new table

When the target table doesn't exist yet, CREATE TABLE name AS SELECT … makes it from the query's columns and fills it with the query's rows in one statement. It is quick for a backup or a scratch copy, but the new table only gets column names and types: the primary key, NOT NULL, defaults and indexes of the source are not copied.

create-table-as.sql

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    total REAL NOT NULL
);
INSERT INTO orders (id, customer, total) VALUES
    (1, 'Ana', 40.0),
    (2, 'Ben', 15.5),
    (3, 'Cleo', 99.9);

-- Creates big_orders and fills it in one statement
CREATE TABLE big_orders AS
SELECT id, customer, total
FROM orders
WHERE total > 20;

SELECT * FROM big_orders;

-- Column names and types come across; keys and NOT NULL don't
SELECT name, type, "notnull", pk
FROM pragma_table_info('big_orders');

-- So a duplicate id goes straight in
INSERT INTO big_orders (id, customer, total) VALUES (1, 'Ana', 40.0);
SELECT id, count(*) AS n FROM big_orders GROUP BY id;

Output

pragma_table_info shows notnull and pk as 0 for every column, and SQLite even records id as INT rather than INTEGER, so it is no longer the rowid key. The duplicate insert succeeds and id 1 appears twice. PostgreSQL and MySQL behave the same way: their CREATE TABLE … AS copies also accepted a duplicate id. When the copy needs its keys, create the table first with the constraints you want, then fill it with INSERT INTO … SELECT from section 1.

5Copy one column into another in the same table

Copying a value from one column to another in the same row isn't an insert at all: the rows already exist, so it is an UPDATE. SET b = a copies column a into b on every row the WHERE matches, and the right-hand side can be any expression over that row.

copy-column.sql

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    login TEXT
);
INSERT INTO users (id, email, login) VALUES
    (1, '[email protected]', NULL),
    (2, '[email protected]', 'benny'),
    (3, '[email protected]', NULL);

-- Copy one column into another, row by row
UPDATE users SET login = email WHERE login IS NULL;
SELECT * FROM users;

-- The right-hand side can be any expression on the same row
UPDATE users SET login = upper(substr(email, 1, instr(email, '@') - 1));
SELECT id, login FROM users;

-- Every right-hand side reads the row as it was, so this swaps
CREATE TABLE names (first TEXT, last TEXT);
INSERT INTO names VALUES ('Lovelace', 'Ada');
UPDATE names SET first = last, last = first;
SELECT * FROM names;

Output

The first update fills login only where it was NULL, so Ben keeps benny. The second rewrites every login from the part of the email before the @. The swap works because each right-hand side sees the row before the update, which is standard SQL and also holds in PostgreSQL and SQL Server; MySQL applies assignments left to right, so the same statement there leaves both columns equal. To copy a column from a different table, matched on an id, see update from another table.

6Other databases

INSERT INTO … SELECT is standard SQL and runs unchanged everywhere. Creating a new table from a query is where the databases differ: SQL Server has no CREATE TABLE … AS and uses SELECT … INTO, PostgreSQL accepts both, and MySQL reads SELECT … INTO as assigning to variables. Each form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server (through Babelfish).

Oracle uses INSERT INTO … SELECT and CREATE TABLE … AS SELECT the same way; it has no SELECT … INTO for tables either.

Syntax	Database	Notes
INSERT INTO t (cols) SELECT … FROM s	All: SQLite, PostgreSQL, MySQL, SQL Server	Copies into an existing table; the portable answer
CREATE TABLE t AS SELECT …	SQLite, PostgreSQL, MySQL	Creates and fills t; no primary key or NOT NULL copied
SELECT … INTO t FROM s	SQL Server, PostgreSQL	Creates t (fails if it exists); no primary key copied. MySQL rejects it: Undeclared variable
CREATE TABLE t (LIKE s INCLUDING ALL)	PostgreSQL	Empty copy with the primary key and defaults; fill it with INSERT … SELECT
CREATE TABLE t LIKE s	MySQL	Empty copy with the keys and indexes; fill it with INSERT … SELECT

Frequently asked questions

How do I insert the results of a SELECT into an existing table?

Write the query where the VALUES list would go: INSERT INTO target (a, b) SELECT x, y FROM source WHERE ...;. Leave out VALUES and the parentheses around the query; INSERT INTO b (id, name) VALUES (SELECT id, name FROM a) is a syntax error (SQLite reports near "SELECT": syntax error). This form works in SQLite, PostgreSQL, MySQL and SQL Server.

What is the difference between SELECT INTO and INSERT INTO SELECT?

INSERT INTO t SELECT ... adds rows to a table that already exists and keeps its keys and constraints. SELECT ... INTO t FROM ... (SQL Server, PostgreSQL) creates a new table t from the query and fails if t already exists. In MySQL, SELECT ... INTO only assigns to variables, so SELECT id, customer INTO big2 FROM orders fails with Undeclared variable: big2; use CREATE TABLE big2 AS SELECT ... there.

Why does INSERT … SELECT say the number of values doesn't match?

The SELECT must return exactly one value per listed column, matched by position, not by name. Selecting two columns into a three-column list fails in SQLite with 2 values for 3 columns. Add the missing value as a literal or NULL in the select list, or remove the column from the target list so it gets its default.

How do I use ON CONFLICT with INSERT … SELECT in SQLite?

Add a WHERE clause to the SELECT, even WHERE true: INSERT INTO customers (email, name) SELECT email, name FROM signups WHERE true ON CONFLICT (email) DO NOTHING;. Without it, SQLite reads ON as the start of a join condition and fails with near "DO": syntax error.

How do I copy a table's structure without its rows?

Use a query that returns no rows: CREATE TABLE empty_copy AS SELECT * FROM orders WHERE false; creates the columns with zero rows, but without the primary key or other constraints. To keep the keys, use CREATE TABLE t LIKE s in MySQL or CREATE TABLE t (LIKE s INCLUDING ALL) in PostgreSQL.

How do I swap the values of two columns?

Assign both in one UPDATE: UPDATE names SET first = last, last = first;. In SQLite, PostgreSQL and SQL Server every right-hand side reads the row as it was before the update, so a row (1, 2) becomes (2, 1). MySQL applies the assignments left to right, so the same statement turns (1, 2) into (2, 2); there you need a temporary column or variable.

Run it yourself

Open any of these in the full SQL editor to change the tables, run and share.

SQLite playground