SQLite - INSERT Query

Modern Data Insertion with SQLite

The INSERT statement is used to add new rows of data to a table. Modern SQLite offers powerful and efficient ways to handle data insertion, including handling conflicts and inserting multiple rows at once.

Basic INSERT Syntax

The fundamental syntax requires specifying the table, the columns you’re inserting into, and the values for those columns.

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Best Practice: Always explicitly list the column names. This makes your query more readable and resilient to future changes in the table structure (like adding a new column with a default value).

While you can omit the column list if you provide a value for every column in the correct order, it is fragile and not recommended for production code.

-- This is less robust and should be avoided.
INSERT INTO table_name VALUES (value1, value2, ...);

Example: Populating an employees Table

Let’s start with a sample employees table.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY, -- Automatically increments
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    age INTEGER NOT NULL,
    salary REAL
);

Now, let’s insert a single employee record:

INSERT INTO employees (name, email, age, salary)
VALUES ('Paul Smith', 'paul.smith@example.com', 32, 75000.00);

Inserting Multiple Rows

To insert multiple rows in a single, efficient statement, you can provide a comma-separated list of value tuples.

INSERT INTO employees (name, email, age, salary)
VALUES
    ('Allen Johnson', 'allen.j@example.com', 25, 60000.00),
    ('Teddy Brown', 'teddy.b@example.com', 23, 55000.00),
    ('Mark Davis', 'mark.d@example.com', 25, 95000.00);

Handling Conflicts: The UPSERT

What happens if you try to insert a record that violates a UNIQUE constraint (like email)? By default, SQLite will raise an error. However, modern SQLite provides a powerful ON CONFLICT clause (also known as an “UPSERT”) to handle these situations gracefully.

The syntax allows you to specify an action if the insertion fails due to a constraint violation.

INSERT INTO table_name (column_list)
VALUES (value_list)
ON CONFLICT(target_column) DO UPDATE SET ...;

Let’s try to insert a new record for ‘Mark Davis’ but also update his salary if his email already exists.

-- This will update Mark's salary because his email is already in the table.
INSERT INTO employees (name, email, age, salary)
VALUES ('Mark Davis', 'mark.d@example.com', 26, 98000.00)
ON CONFLICT(email) DO UPDATE SET
    salary = excluded.salary,
    age = excluded.age;

Here, excluded.salary refers to the salary value from the INSERT statement that was attempted. Another option is to do nothing:

-- If a user with this email already exists, do nothing.
INSERT INTO employees (name, email, age, salary)
VALUES ('Kim West', 'kim.w@example.com', 22, 50000.00)
ON CONFLICT(email) DO NOTHING;

Populating from Another Table

You can insert data into a table by selecting it from another. This is extremely useful for data migration, archiving, or creating summary tables.

Imagine we have a new_hires table and we want to import them into our main employees table.

CREATE TABLE new_hires (name TEXT, email TEXT, age INTEGER, start_salary REAL);
INSERT INTO new_hires VALUES ('James Miller', 'james.m@example.com', 24, 52000.00);

-- Now, insert from new_hires into employees.
INSERT INTO employees (name, email, age, salary)
SELECT name, email, age, start_salary
FROM new_hires
WHERE start_salary > 50000.00; -- Optionally filter the data being inserted

Practical Considerations

• Transactions: When performing multiple INSERT statements, wrap them in a BEGIN TRANSACTION; … COMMIT; block. This is significantly faster and ensures that all insertions succeed or none do (atomicity).
• Performance: Inserting multiple rows in a single statement is much more performant than running individual INSERT statements in a loop.