SQLite: INSERT Statement

This SQLite post explains how to use the SQLite INSERT statement with syntax and examples.

Description

The SQLite INSERT statement is used to insert a single record or multiple records into a table in SQLite.

Syntax

The syntax for the SQLite INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... ),
(expression1, expression2, ... ),
...;

Or...

The syntax for the INSERT statement when inserting multiple records using a sub-select in SQLite is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
[WHERE conditions];

Parameters or Arguments

table

The table to insert the records into.

column1, column2

The columns in the table to insert values.

expression1, expression2

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

source_table

The source table when inserting data from another table.

WHERE conditions

Optional. The conditions that must be met for the records to be inserted.

Note

• When inserting records into a table using the SQLite INSERT statement, you must provide a value for every NOT NULL column.
• You can omit a column from the SQLite INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

The simplest way to create a SQLite INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO employees
(employee_id, last_name, first_name, favorite_website)
VALUES
(1, 'Smith', 'John', 'AODBA.com');

This SQLite INSERT statement would result in one record being inserted into the employees table. This new record would have an employee_id of 1, last_name of 'Smith', first_name of 'John', and favorite_website of 'AODBA.com'.

Example - Insert more than one record using VALUES keyword

You can also insert more than one record at a time in SQLite using the VALUES keyword. This is done by comma delimiting the records that you wish to insert.

For example:

INSERT INTO employees
(employee_id, last_name, first_name, favorite_website)
VALUES
(1, 'Smith', 'John', 'AODBA.com'),
(2, 'Mark', 'Dale', 'mySite.com');

This SQLite INSERT statement would result in two records being inserted into the employees table. The first record is identified with an employee_id of 1. All values for the first record are enclosed in parentheses.

You then separate the records with a comma and list the values for the next record which is again enclosed in parentheses.

Example - Using sub-select

You can also create more complicated SQLite INSERT statements using sub-selects.

For example:

INSERT INTO temp
(temp_employee_id, temp_last_name, temp_first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id  50;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQLite SELECT statement before performing the insert.

SELECT COUNT(*)
FROM employees
WHERE employee_id  50;