How to insert record in SQLite? Explain with example.

SQLite Insert Query

In SQLite INSERT statement is used to create new rows in 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 statement using values clause

INSERT INTO table_name(column_name [, ...])

VALUES (new_value [, ...]);

The above SQLite insert query syntax is used to insert values in table and its having following properties.

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

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

new_value: Value for 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 following query.

CREATE TABLE products

(

Product_id INTEGER PRIMARY KEY,

Product_name VARCHAR NOT NULL,

Qty INTEGER

);

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

INSERT INTO products(product_id,product_name,qty)

Values(120,'AutoCAD',50);

In above example, we are inserting data into 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 SQLite SELECT statement like as shown below.

sqlite>SELECT * FROM products;

Product_id  Product_name  Qty

----------  ------------  ----------

120         AutoCAD       50

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

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

INSERT INTO table_name VALUES (new_value [, ...]);

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 table without specifying column list in sqlite.

INSERT INTO products VALUES (102,'AutoCAD',58);

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

sqlite>SELECT * FROM products;

Product_id  Product_name  Qty

----------  ------------  ----------

102         AutoCAD       58

120         AutoCAD       50

SQLite Insert Multiple Rows

SQLite offers 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 sqlite insert statement.

INSERT INTO table_name [(column-list)]

values(new_value1 [, ...]),

(new_value2 [, ...]),...

(new_valueN [, ...]);

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 products table.

INSERT INTO products

VALUES (103,'Rubber-band',230),

(104,'Cosmetics',1200),

(105,'Eye make up',102);

Above sqlite query will insert 3 rows in products table with a single INSERT statement like as shown below.

sqlite>SELECT * FROM products;

Product_id  Product_name  Qty

----------  ------------  ----------

102         AutoCAD       58

103         Rubber-band   230

104         Cosmetics     1200

105         Eye make up   102

120         AutoCAD       50

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.

INSERT INTO table_name [(column-list)] select-statement

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.

INSERT INTO products(Product_id, Product_name,Quantity)

SELECT Product_id, Product_name, Qty

FROM _products_old;

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