Importing a CSV Into an Existing SQLite Table Step by Step

Importing into an empty table that you just created is easy. Importing into a table that already has rows, a primary key and a defined column order is where the .import command shows how little it checks. It maps by position, it does not read the header, and its idea of an error is a message on the screen. This article tries the four things that go wrong, a duplicate key, too few columns, too many and a different order, with real output, and then replaces the plain import with a staging table and a real INSERT, which handles all four. The command itself is described in using the .import command, and the bigger picture is in the SQLite import guide. The behavior of .import on an existing table is stated in the command line shell documentation, and the table definition rules are in the CREATE TABLE reference.

What you need

The table, already loaded with four customers from the first file, and the new data in a second file. The table:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL
);

The second file has two records. Id 4 already exists with a different name, and id 5 is new:

id,name,email,signup_date,balance
4,Cy Dubois (updated),[email protected],2025-03-01,99.00
5,Eli Fox,[email protected],2025-03-03,6.00

The first problem: duplicate keys

A plain import of this file into the existing table, with --skip 1 for the header:

rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)" ".import --csv --skip 1 customers.csv customers"
sqlite3 shop.db ".import --csv --skip 1 customers-extra.csv customers" "SELECT id, name FROM customers ORDER BY id" 2>&1

customers-extra.csv:2: INSERT failed: UNIQUE constraint failed: customers.id

The shell printed customers-extra.csv:2: INSERT failed: UNIQUE constraint failed: customers.id, and only that line, because the first record was the duplicate and the command had nothing more to say. The second record, id 5, loaded. The old row for id 4 was left alone, so the table now holds the old Cy Dubois and the new Eli Fox. That is not an update and it is not an all or nothing failure. The exit status of the shell was 1, which a script can catch. If you load a file every night and some of its ids were loaded yesterday, this is what happens to the changes in them: they are dropped, with a message that nobody reads.

The second and third problems: the wrong number of columns

I made two more files. The first has only id, name and email. The second has an extra source column:

rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)"
sqlite3 shop.db ".import --csv --skip 1 customers-fewer.csv customers" "SELECT id, name, email, signup_date, balance FROM customers" ".nullvalue NULL" "SELECT id, signup_date, balance FROM customers" 2>&1

customers-fewer.csv:2: expected 5 columns but found 3 - filling the rest with NULL
7|Gus|[email protected]||
7|NULL|NULL

For the short record, SQLite printed expected 5 columns but found 3 - filling the rest with NULL and stored the record with signup_date and balance as NULL; the second query, with .nullvalue NULL set, shows that. For the long one:

rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)"
sqlite3 shop.db ".import --csv --skip 1 customers-more.csv customers" "SELECT * FROM customers" 2>&1

customers-more.csv:2: expected 5 columns but found 6 - extras ignored
8|Hal|[email protected]|2025-03-04|1.0

expected 5 columns but found 6 - extras ignored, and the row loaded without the sixth field. Both messages go to standard error, and both leave the exit status at 0. The shell treats the width of the table as the truth and bends the file to fit it. If the extra column is a new field the sender added on purpose, you have just discarded it.

The fourth problem: the order

The columns of a file in a different order than the table go into the wrong columns, because .import does not look at the names:

printf 'name,id,email,signup_date,balance\nZed,9,[email protected],2025-05-05,1\n' > reordered.csv
rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)"
sqlite3 shop.db ".import --csv --skip 1 reordered.csv customers" "SELECT id, name FROM customers" 2>&1

reordered.csv:2: INSERT failed: datatype mismatch

reordered.csv:2: INSERT failed: datatype mismatch. The file starts with name,id,..., so the name Zed was bound to the id INTEGER PRIMARY KEY column, which can only hold an integer, and the insert failed. That is the lucky version. If the first two columns had both been text, the data would have gone in swapped, with a 1 in the name column, and no message. A reordered export is the standard consequence of someone adding a column to a report. The same silent failure exists in other engines, and PostgreSQL's HEADER match is the check that SQLite lacks.

The fix: stage it, then insert with names

Import into a temporary table, where SQLite creates the columns from the header, and move the rows with an INSERT ... SELECT that names the columns on both sides. This is the reordered file:

rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)"
sqlite3 shop.db ".import --csv --schema temp reordered.csv incoming" "INSERT INTO customers (id, name, email, signup_date, balance) SELECT id, name, email, signup_date, balance FROM temp.incoming" "SELECT id, name FROM customers"

9|Zed

The row loaded as id 9, name Zed. The import created temp.incoming with the file's own column names, so the SELECT picked them by name, and the order of the file stopped mattering. If a column the select names is missing from the file, the statement fails with no such column, which is the check that the plain import does not have: a file that has lost or renamed a column cannot be loaded by accident.

Turning duplicates into updates

For the duplicate key problem, the staging table also solves what to do with ids that exist. INSERT ... ON CONFLICT is SQLite's upsert, documented on the UPSERT page, and it names the columns to overwrite:

rm -f shop.db; sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, email TEXT, signup_date TEXT, balance REAL)" ".import --csv --skip 1 customers.csv customers"
sqlite3 shop.db <<'SQL'
.import --csv --schema temp customers-extra.csv incoming
INSERT INTO customers SELECT * FROM temp.incoming WHERE true
  ON CONFLICT(id) DO UPDATE SET name = excluded.name, balance = excluded.balance;
SELECT id, name, balance FROM customers ORDER BY id;
SQL

1|Ana Silva|120.5
2|Smith, Jane|0.0
3|Bo Chen|
4|Cy Dubois (updated)|99.0
5|Eli Fox|6.0

Id 4 is now Cy Dubois (updated) with a balance of 99.0, Eli Fox arrived as id 5, and ids 1 to 3 are untouched. Only the columns you list in DO UPDATE SET change, so Cy's email and signup_date kept their old values. The WHERE true in the select is there because of a parsing ambiguity that the UPSERT page describes between ON in a join and ON CONFLICT; with a SELECT that has a WHERE clause the ambiguity does not arise.

The same table with INSERT OR IGNORE skips duplicates silently and with INSERT OR REPLACE deletes and reinserts the row, which resets any column not in the file and is rarely what you want for a refresh. Name the policy, and keep it in the script.

How to tell it worked

Compare the count with what you expect: the old row count plus the number of new ids. For updates, select the rows you changed and compare a few values with the file. Print the temporary table's count before the insert, so the log says how many rows arrived, how many were new and how many were updates. A short query tells you: SELECT count(*) FROM temp.incoming WHERE id IN (SELECT id FROM customers) is the number of conflicts.

When a plain import is the right thing

If the table is empty and the file's layout is the table's, importing directly is fine, and it is the fastest path at about 2 seconds for 2,000,000 rows. If the file is one of a series with a fixed layout that you control, and the keys never repeat, such as monthly files with the month in the key, a direct import with a count check is enough, and the loop in loading many CSV files into one database uses exactly that. And if the table has no constraints at all, an import cannot fail on a duplicate, though it will happily load the same file twice, so keep a record of which files you have loaded.