1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

INSERT INTO SELECT in SQL: Copy Rows Between Tables Safely

INSERT INTO SELECT copies the result of a query straight into an existing table, with no loop and no export. Learn the syntax, copying chosen columns, filtering and joining, building summary tables, avoiding duplicates on a rerun, upserts, the silent column order bug, and why it was 18 times faster than inserting row by row in our test.

9 min read
INSERT INTO SELECT in SQL: copy rows between tables safely

Key takeaways

  • INSERT INTO SELECT appends the rows returned by a query to a table that already exists.
  • Always list the target columns. Without them, SQLite silently stored a customer name in an amount column in our test.
  • Running the same copy twice failed on the primary key; NOT EXISTS or INSERT OR IGNORE make it safe to rerun.
  • Copying 200,000 rows took 35 ms as one statement versus 625 ms row by row from Python.

Archiving old orders, filling a reporting table, migrating data into a new schema, seeding a test database: all of these mean copying rows from one table to another. Pulling the rows into your application and inserting them one at a time works, but it is slow and fragile. INSERT INTO SELECT does the whole job inside the database in one statement.

We ran every query on SQLite 3.50.4 through Python. The syntax is standard SQL and works the same way in PostgreSQL, MySQL and SQL Server, apart from the dialect notes near the end, which we did not run on those databases.

The INSERT INTO SELECT syntax

INSERT INTO target_table (col1, col2, col3)
SELECT a, b, c
FROM source_table
WHERE condition;

The SELECT can be anything a normal query can be: filtered, joined, grouped, sorted, or built with a CTE. Its columns are matched to the target columns by position, not by name, and the target table must already exist. There is no VALUES keyword.

Our test tables

CREATE TABLE orders  (id INTEGER PRIMARY KEY, customer TEXT, amount REAL, status TEXT, created TEXT);
CREATE TABLE archive (id INTEGER PRIMARY KEY, customer TEXT, amount REAL, status TEXT, created TEXT);
CREATE TABLE summary (customer TEXT PRIMARY KEY, orders INT, total REAL);
CREATE TABLE customers (name TEXT PRIMARY KEY, country TEXT);
-- 1,000 random orders across five customers in 2026

Copy all columns

INSERT INTO archive
SELECT * FROM orders WHERE created < '2026-06-01';
-- archived rows 456

When the two tables have identical columns in identical order, SELECT * works. The cursor’s row count reported 456 rows copied, and SELECT COUNT(*) FROM archive agreed. This is convenient for archive tables that mirror the live one, but it breaks the moment someone adds a column to either table, so prefer explicit columns in anything that lives longer than a one-off script.

Copy chosen columns, with defaults

CREATE TABLE t3 (customer TEXT, amount REAL, note TEXT DEFAULT 'copied');
INSERT INTO t3 (customer, amount)
SELECT customer, amount FROM orders LIMIT 1;
-- [('Chen', 474.45, 'copied')]

Name the target columns, then select the same number of values in the same order. Columns you leave out get their DEFAULT, or NULL if they have none; a NOT NULL column without a default would make the insert fail. Our SQL constraints guide covers defaults and NOT NULL.

Copy rows with a JOIN

CREATE TABLE in_orders (id INTEGER, customer TEXT, amount REAL);
INSERT INTO in_orders
SELECT o.id, o.customer, o.amount
FROM orders o
JOIN customers c ON c.name = o.customer
WHERE c.country = 'IN';
-- join copy 399

The SELECT can combine tables, so you can copy only the orders from customers in India without first exporting customer lists. Any join type works; see our SQL joins guide for the differences.

Build a summary table from GROUP BY

INSERT INTO summary (customer, orders, total)
SELECT customer, COUNT(*), ROUND(SUM(amount), 2)
FROM orders WHERE status = 'paid'
GROUP BY customer;
-- [('Ben', 108, 27581.63), ('Dara', 109, 27291.61), ('Chen', 99, 24520.15),
--  ('Asha', 96, 24236.96), ('Eli', 93, 23611.36)]

Aggregated results can be stored, which is how many reporting and dashboard tables are built: run an expensive GROUP BY once at night, then read the small summary table all day.

The rerun problem

INSERT INTO archive SELECT * FROM orders WHERE created < '2026-06-01';
-- second run: UNIQUE constraint failed: archive.id

INSERT INTO SELECT always appends. Running the same archive job twice tried to insert the same 456 ids again, and the primary key stopped it. That error is the lucky case. If the archive table had no primary key, the second run would have silently doubled every row, and every report built on it would be wrong.

Skip rows that are already there

INSERT INTO archive
SELECT * FROM orders o
WHERE created < '2026-07-01'
  AND NOT EXISTS (SELECT 1 FROM archive a WHERE a.id = o.id);
-- not exists added 93

INSERT OR IGNORE INTO archive
SELECT * FROM orders WHERE created < '2026-08-01';
-- or ignore added 97

Extending the archive to June added only the 93 new rows, because NOT EXISTS filtered out ids already archived. This works in every database and is easy to read. INSERT OR IGNORE is SQLite’s shortcut that skips any row violating a unique constraint; MySQL writes it INSERT IGNORE, and PostgreSQL uses ON CONFLICT DO NOTHING. Be careful: ignore-style options skip all constraint errors, not just the duplicate you expected. For the trade-offs between EXISTS and IN, see our EXISTS vs IN guide.

Upsert from a SELECT

INSERT INTO summary (customer, orders, total)
SELECT customer, COUNT(*), ROUND(SUM(amount), 2)
FROM orders WHERE true
GROUP BY customer
ON CONFLICT(customer) DO UPDATE SET
  orders = excluded.orders, total = excluded.total;
-- upsert touched 5

To refresh a summary instead of failing, combine INSERT INTO SELECT with an upsert: new customers are inserted, existing ones updated, using excluded to refer to the incoming values. One SQLite quirk cost us a run: without the WHERE true, the parser read ON CONFLICT as part of the SELECT and reported near "do": syntax error. The SQLite documentation describes this ambiguity; adding any WHERE clause resolves it. More on this pattern in our SQL upsert guide.

The silent column order bug

CREATE TABLE t2 (amount REAL, customer TEXT);
INSERT INTO t2 SELECT customer, amount FROM orders LIMIT 2;
SELECT * FROM t2;
-- [('Chen', '474.45'), ('Asha', '412.42')]

Columns are matched by position, so selecting customer, amount into a table defined as amount, customer put names into the amount column and amounts into the customer column. SQLite’s flexible typing accepted it without a single warning. PostgreSQL and SQL Server would usually reject the text in a numeric column, but if both columns were the same type, every database would accept swapped data silently. The only reliable defence is to list the target columns every time.

Tip: write the target column list and the SELECT list on matching lines, one column per line, so a reviewer can check them side by side.

CREATE TABLE AS SELECT: when the table does not exist yet

CREATE TABLE copy_as AS SELECT * FROM orders WHERE 0;
-- CREATE TABLE copy_as(
--   id INT, customer TEXT, amount REAL, status TEXT, created TEXT )

If the target does not exist, CREATE TABLE ... AS SELECT (SQL Server: SELECT ... INTO new_table) creates it from the query. WHERE 0 copies the structure with no rows. Look closely at the result, though: the original id INTEGER PRIMARY KEY became plain id INT. The primary key, constraints, defaults and indexes were not copied. For a real table, create it properly first and then use INSERT INTO SELECT.

Moving rows safely

BEGIN;
INSERT INTO archive (id, customer, amount, status, created)
SELECT id, customer, amount, status, created FROM orders WHERE created < '2026-06-01';
DELETE FROM orders WHERE created < '2026-06-01';
COMMIT;

There is no MOVE statement. Copy, then delete with exactly the same condition, inside one transaction so a failure halfway leaves both tables unchanged. If new matching rows can arrive between the two statements, filter the DELETE by the ids you copied instead of repeating the date condition.

Speed: one statement vs a loop

Method, 200,000 rows Time
One INSERT INTO dst SELECT * FROM src 35 ms
Fetch all rows in Python, insert each one 625 ms

The single statement was about 18 times faster, and that was with an in-memory database on the same machine. With a network between the application and the database, a row-by-row loop pays a round trip for every insert and the gap grows much larger. Keep set-based work in SQL; our SQL query optimization guide has more examples.

Dialect notes

  • MySQL: INSERT IGNORE and ON DUPLICATE KEY UPDATE; you can also copy across databases with db_name.table.
  • PostgreSQL: ON CONFLICT (col) DO NOTHING / DO UPDATE, plus RETURNING to see inserted rows.
  • SQL Server: SELECT ... INTO new_table creates a table; use MERGE or NOT EXISTS for upserts. Large inserts fill the transaction log, so batch them.
  • SQLite: INSERT OR IGNORE, ON CONFLICT with the WHERE rule above, and RETURNING since version 3.35.

Final thoughts

INSERT INTO SELECT is the fastest and simplest way to copy data inside a database. List the target columns every time, decide what a rerun should do before the first run, use NOT EXISTS or an upsert to make jobs repeatable, and wrap copy-then-delete in a transaction. Those habits prevent the swapped columns, doubled rows and half-moved data that turn a quick copy into a data-quality incident.

Frequently asked questions

What does INSERT INTO SELECT do?

It runs a SELECT query and inserts every row it returns into an existing table in one statement, matching columns by position. Existing rows in the target are kept.

Does the target table need to exist?

Yes. To create a new table from a query, use CREATE TABLE AS SELECT, or SELECT INTO in SQL Server, but note that keys, constraints and indexes are not copied.

How do I avoid inserting duplicate rows?

Add a NOT EXISTS condition that checks the target for each row, or rely on a unique key with an ignore or ON CONFLICT DO NOTHING option, depending on your database.

Do the column names have to match?

No. Columns are matched by position, not name. That is why you should always list the target columns explicitly, so swapped columns cannot slip through.

Is INSERT INTO SELECT faster than inserting rows in a loop?

Much faster. In our SQLite test, copying 200,000 rows took 35 ms as one statement and 625 ms when inserting each row from Python.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →