SQLite Insert Query

Here we will learn SQLite insert query with example and SQLite insert multiple rows, SQLite insert select from another table with example.

SQLite Insert Query

In SQLite INSERT statement is used to create new rows in the specified table. There are two meaningful versions of the SQLite insert statement. The first version uses a VALUES clause to specify a list of values to insert. 

SQLite Insert Syntax

Following is the syntax of SQLite insert statement using the values clause.

 

The above SQLite insert query syntax is used to insert values in a table and it's having the following properties.

 

table_name: table name in which you want to insert data.

 

column_name: Here you have to specify the name of columns separated by, the symbol in which you want to insert data.

 

new_value: Value for the column that you specify.

SQLite Insert Query Example

Here note that both lists must have the same number of items. A single new row is created and each value is recorded into its respective column. The columns can be listed in any order, just as long as the list of columns and the list of values line up correctly. Any columns that are not listed will receive their default values.

 

Now, for illustrating insert, let’s get create one table called PRODUCT using the following query.

 

Now let’s look at the example of inserting data in SQLite using insert statement.

 

In the above example, we are inserting data into the products table. Here note that text literal is specified within single quotes.

 

Now, let’s check whether that data is inserted properly or not by using the SQLite SELECT statement like as shown below.

 

Here basically list of the column is optional. We can also insert data into the table without specifying the list of columns. 

 

Following is the syntax of inserting data to the table without specifying the list of columns in SQLite.

 

The trick with this format is that the number and order of values must exactly match number and order of columns in the table definition.

 

In SQLite, this format is harder to maintain within application source code, because if table definition changes then we have to manually do changes in source code.

 

Therefore it is good practice to always include the column-list in an INSERT statement also known as performing a full insert.

 

Now let’s look at the example of inserting data into a table without specifying a column list in SQLite.

 

Now, let’s check whether that data is inserted properly or not by using SQLite SELECT statement as shown below.

 

SQLite Insert Multiple Rows

SQLite offers a feature to insert multiple rows in a single INSERT statement. 

SQLite Insert Multiple Rows Syntax

Following is the syntax of inserting multiples rows in a table using the SQLite insert statement.

 

Here we have to write values keyword only once and then we have to give value list separated by comma (,). 

SQLite Insert Multiple Rows Example

Now, let’s look at the example of SQLite inserting multiple rows of data into the products table.

 

The above SQLite query will insert 3 rows in the products table with a single INSERT statement like as shown below.

 

SQLite INSERT using SELECT Statement

In SQLite, we can insert the result of a SELECT query into another table. Following is the syntax of inserting select query result into another table in SQLite.

 

Here note that the select-statement should return a result with the same number and order as the columns specified in the column-list.

 

Now let’s look at example of Inserting data with SELECT in SQLite.

 

This is how we can sqlite insert statement in our applications based on our requirements.