Copy SQLite Tables with Indexes

I recently had to copy some tables from one SQLite database to another. The tables had indexes and foreign key constraints on them, and I wanted to copy those as well. I found a few different ways to do this, but none of them worked perfectly for me. I ended up writing a tiny script to do it, and I thought I’d share it here.

All the examples below assume we have two databases, old.db and new.db, and we want to copy the posts table from old.db to new.db.

Disclaimer: Please only run these commands on a copy of the database. I am not responsible for any data loss or corruption.

Table of Contents

• TL;DR
• The Simple Way
• Using .dump
• Using .schema + .dump
• Alternative Method Using .schema + .dump
• Final Step

TL;DR

# Copying table `posts` from `old.db` to `new.db`.
(
  echo "begin;"
  sqlite3 old.db ".schema posts"
  sqlite3 old.db ".dump posts --data-only"
  echo "commit;"
) | sqlite3 new.db

The Simple Way

We can attach both the databases into a sqlite session and then create a table in the new database with a select * query from the old one. This will copy the rows but not the primary key, indexes, or constraints.

sqlite3 new.db "attach 'old.db' as old; create table posts as select * from old.posts;"

Using .dump

The method I found on StackOverflow was to use the .dump command to dump the table as SQL statements and pipe that into the new database. This will copy the primary key and the unique indexes defined as constraints, but not any other indexes.

sqlite3 old.db ".dump posts" | sqlite3 new.db

Using .schema + .dump

The method I ended up using was to use the .schema command to recreate the table structure as well as indexes, and then use .dump to get the data. This will copy the primary key, unique indexes, and any other indexes defined on the table.

The only problem with this method is that .dump also includes a CREATE TABLE statement which will fail because the table is already created by the .schema command. To get around this, I piped the output of .schema into sed to replace CREATE TABLE with CREATE TABLE IF NOT EXISTS.

sqlite3 old.db ".schema posts" | sqlite3 new.db
sqlite3 old.db ".dump posts" | sed 's/^CREATE TABLE /CREATE TABLE IF NOT EXISTS /' | sqlite3 new.db

We can further wrap this up in a transaction so that if any of the statements fail, the whole thing will be rolled back.

(
  echo "begin;"
  sqlite3 old.db ".schema posts"
  sqlite3 old.db ".dump posts" | sed 's/^CREATE TABLE /CREATE TABLE IF NOT EXISTS /'
  echo "commit;"
) | sqlite3 new.db

Alternative Method Using .schema + .dump

I found this method shortly after writing this blog post.

.dump supports a --data-only flag that only outputs INSERT statements for each row in the table.

$ sqlite
SQLite version 3.44.2 2023-11-24 11:41:44
sqlite> .help dump
.dump ?OBJECTS?          Render database content as SQL
   Options:
     --data-only            Output only INSERT statements
     --newlines             Allow unescaped newline characters in output
     --nosys                Omit system tables (ex: "sqlite_stat1")
     --preserve-rowids      Include ROWID values in the output
   OBJECTS is a LIKE pattern for tables, indexes, triggers or views to dump
   Additional LIKE patterns can be given in subsequent arguments

We can combine this with .schema and wrap everything in a transaction to get the same result as above.

(
  echo "begin;"
  sqlite3 old.db ".schema posts"
  sqlite3 old.db ".dump posts --data-only"
  echo "commit;"
) | sqlite3 new.db

This method is more straightforward and doesn’t require any sed magic, but it might be a little slower, especially if you have many indexes on the table, since the indexes are created before INSERT in this method.

I would personally go with this method.

Final Step

Since foreign key constraint checks are disabled by default in SQLite, it’s always a good idea to run a full integrity check on the new database to make sure everything is in order.

sqlite3 new.db "pragma integrity_check;"

If this prints ok, then we’re good to go!