SQLite Insert Query

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.

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 users:

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 table1 into table2. The columns need to match between the two tables.

Related Links

• 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.