SQLite: Copy a Table From One Database to Another, Properly

Attach the second database to your current connection and copy with an ordinary INSERT … SELECT. Two statements, no export files, no CSV round trip. The part every short answer leaves out is that this copies rows and nothing else: not indexes, not constraints, not triggers, not the exact column types you thought you had.

The Stack Overflow answer for this is fifteen years old and still correct, which is a compliment to SQLite. It is also incomplete in a way that produces a table that looks right and behaves differently, so it is worth doing the whole job rather than the first two lines of it.

The two-statement version
When the destination table does not exist yet
The correct order: schema first, then rows
The dump route, and when it is the better choice
The things that go wrong
Copying between databases as a habit is usually a signal
How this fits the rest of the stack
FAQ

The two-statement version

Open the destination database and attach the source to the same connection. Every attached database gets a schema name, and you then reference its tables with that prefix.

-- Open the destination first
-- sqlite3 destination.db

ATTACH DATABASE 'source.db' AS src;

-- Table already exists in the destination with a matching schema
INSERT INTO orders SELECT * FROM src.orders;

DETACH DATABASE src;

That is the whole operation when the destination table already exists. SELECT * relies on the column order matching, which is fragile — if either schema changes, the copy silently puts values in the wrong columns. Name the columns instead once this is anything but a one-off:

INSERT INTO orders (id, customer_id, total, created_at)
SELECT id, customer_id, total, created_at FROM src.orders;

The main database is main and you can prefix it explicitly, which is worth doing in any script where both databases contain a table with the same name. INSERT INTO main.orders SELECT * FROM src.orders cannot be misread.

When the destination table does not exist yet

The obvious move is CREATE TABLE ... AS SELECT, and it works, and it is the source of most of the surprises in this article.

ATTACH DATABASE 'source.db' AS src;

CREATE TABLE orders AS SELECT * FROM src.orders;

DETACH DATABASE src;

The rows arrive. Here is what does not:

PRIMARY KEY. The new table has no primary key. If the original used INTEGER PRIMARY KEY as a rowid alias, that behaviour is gone.
NOT NULL, UNIQUE, CHECK, DEFAULT. All dropped. The column that could never be null now can be.
FOREIGN KEY. Dropped, along with any cascade behaviour.
Indexes. Not copied. Queries that were fast are now table scans.
Triggers. Not copied.
Column types. SQLite derives them from the expression rather than the source declaration, so declared types can come out different from what you expect.

So CREATE TABLE AS SELECT is fine for a scratch copy you are about to query once and throw away. It is the wrong tool for anything that will be written to afterwards.

The correct order: schema first, then rows

Copy the definition from the source, run it against the destination, then insert. .schema in the CLI prints the original DDL, including indexes and triggers.

# Get the exact definition, including indexes and triggers
sqlite3 source.db ".schema orders" > orders_schema.sql

# Apply it to the destination
sqlite3 destination.db < orders_schema.sql

# Then copy the rows
sqlite3 destination.db "
ATTACH DATABASE 'source.db' AS src;
INSERT INTO orders SELECT * FROM src.orders;
DETACH DATABASE src;
"

This gets you an identical table rather than a lookalike. One ordering detail: if the schema includes indexes, insert the rows before creating the indexes when the table is large. Building an index once over the finished data is considerably faster than maintaining it across every insert.

If foreign keys are enabled in the destination and the referenced rows are not there yet, wrap the copy in a transaction with the constraint deferred, or copy the parent tables first.

The dump route, and when it is the better choice

The other classic approach is .dump, which writes SQL statements you can replay anywhere.

# One table, schema and data as SQL
sqlite3 source.db ".dump orders" > orders.sql

# Replay into the destination
sqlite3 destination.db < orders.sql

# Whole database
sqlite3 source.db .dump > everything.sql

.dump includes the CREATE TABLE, the indexes, and the INSERT statements, so it does not have the schema-loss problem. Use it when the destination is on another machine, when you want the transfer to be a reviewable text file, or when you are moving to a different database engine and intend to edit the SQL on the way.

Its downsides are size and speed. A dump of a large table is a very large text file, and replaying millions of individual INSERT statements is slower than an ATTACH copy that never leaves the database engine. For same-machine work, ATTACH wins on both counts.

The things that go wrong

Four failures account for nearly all of the trouble here.

database is locked. Another process holds a write lock on one of the databases. SQLite allows one writer at a time. Close the other connection, or if the source is only being read, ensure it is not mid-transaction elsewhere.
A relative path that resolves somewhere unexpected. ATTACH DATABASE 'source.db' is relative to the working directory of the process, not to the destination file. If the attach silently creates an empty database instead of finding yours, that is why — use an absolute path.
Attaching a database that does not exist creates it. Empty, with no tables, and your copy then fails with no such table rather than a helpful message about the file. Check the file exists first.
Generated columns. INSERT INTO t SELECT * FROM src.t fails or misaligns if the table has generated columns, because they cannot be inserted into. Name the real columns explicitly.

One more that is not an error but is a data problem: if both tables have rows with the same primary key, the insert fails on the conflict. INSERT OR IGNORE skips the duplicates and INSERT OR REPLACE overwrites them. Choose deliberately, because those two produce very different results and the difference is invisible afterwards.

Copying between databases as a habit is usually a signal

One-off copies are ordinary: pulling a subset out for analysis, splitting a file, seeding a test fixture. Nothing wrong with any of that.

Doing it on a schedule is different. When a system regularly moves tables between SQLite files, it is usually because SQLite has reached the edge of what it does well: one writer at a time, one file, no network access, and no separation between the application and its data.

The point at which that becomes a real constraint is concurrency. SQLite is genuinely excellent for embedded use, for read-heavy workloads, and for a single application process. Multiple application instances writing to the same data is where it stops being the right tool, and the symptom is not a clean failure — it is database is locked under load, appearing at exactly the times when the system is busiest.

If that is the situation, the move is to a server database rather than a better copy script. Postgres or MySQL as a managed instance gives you concurrent writers, network access, and a backup story, and the migration path from SQLite is well travelled. On RunxBuild both are available as managed instances from $6 on the Basic plan, with connection limits, backups and private networking. That is a different article’s worth of work, but the copy script is a good moment to notice you might need it.

How this fits the rest of the stack

Copying a table between SQLite files is a two-statement job if you bring the schema with it. If you are doing it because one file has become several processes fighting over a write lock, the answer is a server database rather than a smarter script. The RunxBuild hosting calculator prices a managed Postgres or MySQL instance beside the service that talks to it, which is the comparison worth making before writing another sync job.

Useful related references:

FAQ

How do I copy a table between two SQLite databases?

Attach the source to the connection and insert from it: ATTACH DATABASE ‘source.db’ AS src; then INSERT INTO orders SELECT * FROM src.orders; then DETACH DATABASE src. Name the columns explicitly rather than using SELECT * in anything repeated.

Does CREATE TABLE AS SELECT copy the schema?

No. It copies rows and column names only. Primary keys, NOT NULL, UNIQUE, CHECK, DEFAULT, foreign keys, indexes and triggers are all dropped, and declared types can differ. Copy the DDL from .schema first if the table will be written to.

What causes database is locked when copying tables?

Another connection holds a write lock. SQLite permits a single writer at a time. Close other connections or make sure no other process is mid-transaction on either file.

Should I use ATTACH or .dump to move a table?

ATTACH for same-machine copies: it is faster and never leaves the engine. Use .dump when the destination is on another machine, when you want a reviewable text file, or when you are migrating to a different database engine.

Why did ATTACH create an empty database?

ATTACH creates the file if it does not exist, and the path is relative to the process working directory rather than to the destination database. A typo or a wrong working directory produces an empty database and a no such table error. Use absolute paths in scripts.