Once you’ve designed a table, the next natural step is getting real data into it. This article focuses specifically on the practical craft of inserting data — the different techniques, formats, and workflows you’ll actually use day to day, beyond just the bare syntax. If you want a deeper dive purely into the INSERT command’s syntax and conflict-handling options, I’ve covered that in a companion article. Here, I want to focus on the practical side: how you actually get data — from forms, files, other systems — into a SQLite table reliably.
Starting with a Table
Let’s set up something to work with throughout this guide:
CREATE TABLE contacts (
id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT UNIQUE,
phone TEXT,
added_on TEXT DEFAULT CURRENT_TIMESTAMP
);
Inserting a Single Row Manually
The most direct way to add data is typing an INSERT statement by hand, which is exactly what you’d do when adding a one-off record during development or testing:
INSERT INTO contacts (full_name, email, phone)
VALUES ('Nimra Aslam', 'nimra@example.com', '0300-1234567');
Query it back to confirm:
SELECT * FROM contacts;
You’ll see the row with an auto-assigned id and an automatically populated added_on timestamp, since we didn’t provide one and the column has a DEFAULT.
Inserting Data via the Command-Line Shell
If you’re working directly in the sqlite3 shell, you can type INSERT statements one after another, or paste in a block of them at once:
INSERT INTO contacts (full_name, email, phone) VALUES
('Kamran Shah', 'kamran@example.com', '0301-9876543'),
('Rabia Yousaf', 'rabia@example.com', '0333-4567890');
This is fine for small amounts of data, but the moment you’re dealing with dozens or hundreds of records, typing them by hand stops being practical, and you’ll want one of the approaches below instead.
Importing Data from a CSV File
This is one of the most common real-world ways data actually ends up in a SQLite table — you’ve got a spreadsheet or export from another system in CSV format, and you need it in your database. SQLite’s command-line shell has built-in support for this using the .import command.
Suppose you have a file called new_contacts.csv:
full_name,email,phone
Waleed Anjum,waleed@example.com,0345-1112233
Sadia Karim,sadia@example.com,0321-4445566
First, tell SQLite you’re working with CSV format, then import:
.mode csv
.import --skip 1 new_contacts.csv contacts
The --skip 1 flag tells SQLite to skip the header row, since it’s column names, not actual data. This reads every remaining row in the CSV file and inserts it into the contacts table, matching columns by position. It’s remarkably fast, even for files with tens of thousands of rows, since SQLite handles the whole import as a batch operation internally.
One important thing to watch for: .import expects your CSV columns to line up with your table columns in the right order (or you need a table shape that matches exactly). If your CSV has extra columns or a different column order than your table, you’ll either need to reorder the CSV first, create a staging table matching the CSV’s exact shape and then INSERT INTO … SELECT from there into your real table, or preprocess the file before importing.
CREATE TABLE contacts_staging (
full_name TEXT,
email TEXT,
phone TEXT
);
.mode csv
.import --skip 1 new_contacts.csv contacts_staging
INSERT INTO contacts (full_name, email, phone)
SELECT full_name, email, phone FROM contacts_staging;
DROP TABLE contacts_staging;
This staging-table pattern is one I use constantly for real-world imports, because it gives you a safe place to inspect and clean the raw imported data before merging it into your actual production table.
Inserting Data Programmatically from an Application
In practice, most data insertion in real applications happens through code, not by typing SQL manually. Here’s how that looks with a batch of records in Python, which is a pattern you’ll use constantly:
import sqlite3
connection = sqlite3.connect('contacts.db')
cursor = connection.cursor()
new_contacts = [
('Hamza Tariq', 'hamza@example.com', '0302-1231231'),
('Mehak Fatima', 'mehak@example.com', '0311-4564564'),
]
cursor.executemany(
'INSERT INTO contacts (full_name, email, phone) VALUES (?, ?, ?)',
new_contacts
)
connection.commit()
connection.close()
executemany() is specifically optimized for inserting many rows efficiently in one call, and it correctly uses parameterized queries, which protects you from SQL injection when the data is coming from user input, external files, or any source you don’t fully control.
Inserting JSON Data
If your data arrives as JSON — very common with API responses — you’ll typically parse it in your application code first, then insert the extracted fields normally:
import sqlite3
import json
data = '[{"full_name": "Adeel Rashid", "email": "adeel@example.com", "phone": "0333-9998887"}]'
records = json.loads(data)
connection = sqlite3.connect('contacts.db')
cursor = connection.cursor()
for record in records:
cursor.execute(
'INSERT INTO contacts (full_name, email, phone) VALUES (?, ?, ?)',
(record['full_name'], record['email'], record['phone'])
)
connection.commit()
connection.close()
SQLite also has built-in JSON functions if you’d rather store and query JSON directly inside a column, but for structured data like contact records, extracting fields into proper columns (as shown above) is usually the better approach, since it lets you use normal WHERE clauses, indexes, and constraints on those fields.
Handling Duplicate or Conflicting Data During Import
When importing data from an external source, you’ll often hit duplicates — the same email address appearing twice, for instance, which violates our UNIQUE constraint. Rather than letting the whole import fail on the first conflict, use INSERT OR IGNORE to skip conflicting rows quietly:
INSERT OR IGNORE INTO contacts (full_name, email, phone)
VALUES ('Nimra Aslam', 'nimra@example.com', '0300-9999999');
Since nimra@example.com already exists in our table from earlier, this insert is silently skipped rather than throwing an error and halting a larger batch import.
If instead you want new data to overwrite old data on conflict, use the ON CONFLICT upsert pattern:
INSERT INTO contacts (full_name, email, phone)
VALUES ('Nimra Aslam', 'nimra@example.com', '0300-9999999')
ON CONFLICT(email) DO UPDATE SET phone = excluded.phone;
This updates the phone number for the existing Nimra Aslam record if the email already exists, rather than skipping or erroring out. This pattern is extremely useful for syncing data from external sources, where you want to insert new records and update existing ones in the same operation.
Validating Data Before Inserting
Since SQLite’s flexible typing won’t automatically catch every kind of bad data (as covered in the data types article), it’s worth validating in your application code before insertion, especially for data coming from external, less trustworthy sources like file uploads or API calls.
def is_valid_email(email):
return '@' in email and '.' in email.split('@')[-1]
for record in records:
if is_valid_email(record['email']):
cursor.execute(
'INSERT INTO contacts (full_name, email, phone) VALUES (?, ?, ?)',
(record['full_name'], record['email'], record['phone'])
)
else:
print(f"Skipping invalid email: {record['email']}")
Combine this kind of application-level validation with database-level CHECK constraints for genuine defense in depth — catching bad data both before it’s sent and as a final safety net if it somehow gets through anyway.
Using Transactions for Bulk Inserts
If you’re inserting a large batch of records — hundreds or thousands — wrap them in a transaction to dramatically improve performance, since SQLite otherwise commits to disk after every single INSERT by default:
connection = sqlite3.connect('contacts.db')
cursor = connection.cursor()
cursor.execute('BEGIN TRANSACTION')
for record in records:
cursor.execute(
'INSERT INTO contacts (full_name, email, phone) VALUES (?, ?, ?)',
(record['full_name'], record['email'], record['phone'])
)
connection.commit()
The difference in speed here can be dramatic — I’ve seen bulk imports go from taking minutes to taking a couple of seconds just from wrapping the operation in a single transaction instead of letting each insert commit individually.
Verifying Your Data After Insertion
After any significant data import, it’s worth running a few sanity checks rather than just assuming everything went smoothly:
SELECT COUNT(*) FROM contacts;
SELECT * FROM contacts WHERE email IS NULL OR email = '';
SELECT email, COUNT(*) FROM contacts GROUP BY email HAVING COUNT(*) > 1;
That last query would catch duplicate emails if your UNIQUE constraint somehow wasn’t in place, or if duplicates existed under slightly different casing that a case-sensitive UNIQUE constraint didn’t catch. A few minutes spent on checks like this after a big import has saved me from downstream headaches more than once.
Common Mistakes to Avoid
Importing CSV data without skipping the header row, which results in a garbage first row where your column headers get inserted as if they were actual data.
Not wrapping bulk inserts in a transaction, leading to painfully slow imports for anything beyond a small number of rows.
Trusting external data without validation, especially data coming from user uploads or third-party APIs, which can contain malformed, missing, or unexpected values.
Using string concatenation to build INSERT statements from external data instead of parameterized queries, which is both a reliability problem (quotes and special characters in the data will break your SQL) and a serious security vulnerability.
Not having a staging table or intermediate step for messy imports, making it hard to clean or verify data before it lands in your actual production table.
Best Practices Worth Adopting
Use .import for quick CSV loads directly from the SQLite shell, but consider a staging table when the source data’s shape doesn’t cleanly match your target table.
Always use parameterized queries (? placeholders) when inserting data programmatically, never manual string concatenation.
Wrap bulk inserts — anything beyond a handful of rows — inside a transaction to dramatically improve performance.
Validate data at the application level before insertion, and back that up with CHECK and UNIQUE constraints at the database level for genuine defense in depth.
Run basic sanity checks (row counts, null checks, duplicate checks) after any significant import, rather than assuming success just because no error was thrown.
Wrapping Up
Getting data into a SQLite table is something you’ll do constantly, whether that’s a single manual INSERT during development, a CSV import from a spreadsheet someone handed you, or a programmatic batch insert from an application processing thousands of API responses. The mechanics are simple at their core, but doing it reliably — handling duplicates gracefully, validating data, wrapping bulk operations in transactions — is what separates a fragile import script from one you can trust to run against real, messy, real-world data. Build these habits early, and inserting data will be one of the most reliable, least error-prone parts of working with your database.