SQLite Insert Query: A Detailed Guide

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

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

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. When inserting rows, we need to specify the column names we want to insert data into, along with the corresponding values for those columns.

For example:

INSERT INTO users (name, age) VALUES ('John', 30);

This will insert a new row into the users table, populating the name and age columns with the values 'John' and 30 respectively.

We can also insert a row and have the id column automatically populated by an auto-incrementing primary key if we defined it that way in the table schema:

INSERT INTO users VALUES (NULL, 'Mike', 40);

Here we specify NULL for the id column, and SQLite will generate a unique integer id automatically.

To verify the inserts, we can query the users table to see the rows:

SELECT * FROM users;

Which would return:

id          name        age        
----------  ----------  ----------
1           John        30
2           Mike        40

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 when inserting like this.

Summary

In summary, the INSERT statement allows you to add new data rows to a SQLite table. It is a fundamental CRUD operation for populating new data into a database.