The INSERT statement in SQLite is used to insert new rows of data into a table. It allows you to add new records to a table in a database.
What's in this article
Syntax of SQLite Insert Query
The basic syntax for INSERT is:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
This will insert a new row with the specified column values into the table.
You can also insert multiple rows at once:
INSERT INTO table_name (column1, column2, ...) VALUES (row1_value1, row1_value2, ...), (row2_value1, row2_value2, ...), ...;
If you are inserting values into all columns of the table, you can omit the column names:
INSERT INTO table_name VALUES (value1, value2, ...);
Example of SQLite Insert Query
Let’s look at an example to demonstrate SQLite insert.
First, we’ll create a table called
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER );
This creates a new table with columns for id, name, and age.
Now we can insert new rows into this table:
INSERT INTO users (name, age) VALUES ('John', 30); INSERT INTO users VALUES (NULL, 'Mike', 40);
The first INSERT statement provides the column names we want to insert into and the corresponding values.
The second INSERT assumes the values are provided for all columns in the order they were defined. We use
NULL for the ID since it is an auto-incrementing primary key.
To verify the inserts, we can query the users table:
SELECT * FROM users;
Which would return:
id name age ---------- ---------- ---------- 1 John 30 2 Mike 40
So we can see the two new records were successfully inserted.
Inserting With a Select Statement
You can also populate a table by inserting rows from another existing table. This is done using a SELECT statement with INSERT:
INSERT INTO table2 SELECT * FROM table1;
This will insert all rows from
table2. The columns need to match between the two tables.
- SQLite Attach Database – Attaching another database
- SQLite Create Table – Creating a new table
- SQLite Drop Table – Deleting a table
- SQLite Show Tables – Listing tables in a database
- SQLite Operators – Comparison and logical operators
The INSERT statement allows you to populate a SQLite table with new data rows. It is a fundamental statement for adding data to a database.