SQLite - INSERT INTO Statement

SQLite - INSERT INTO Statement

In SQLite the INSERT INTO Statement can be used to insert/add new rows of record in the table in a database.

SQLite INSERT INTO Syntax

In SQLite it is possible to use INSERT INTO in two main ways.

The first way is to only mention the value according to the columns. In this way we do not need to specify the column names.

INSERT INTO table_name VALUES
(value1,value2,value3,...);
                            

The second way is to mention the column names where we want to add the data.

INSERT INTO table_name 
(column_name1,column_name2,column_name3,...)
VALUES (value1,value2,value3,...);
                            

Example:

So let's consider we have created SCHOOL database. And we have created a STUDENTS table in this database.

The STUDENTS table have five columns (ID, NAME, SURNAME, AGE, ADDRESS).

The STUDENTS table also have some data inside it as shown below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal
                            

So Let's say we want to insert a new row in the STUDENTS table.

We can use the following SQLite statement:

INSERT INTO STUDENTS (ID, NAME, SURNAME, AGE, ADDRESS)
VALUES (8, 'Jerry', 'Bann', 23, 'Bonn');
                            

Now when we select the STUDENTS table again, it will look as below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal
8           Jerry       Bann        23          Bonn
                            

Insert Data Only in Selected Columns

In SQLite it is also possible to insert data only in selected column. This can be done as shown below:

INSERT INTO STUDENTS (ID, NAME, SURNAME)
VALUES (9, 'Meg', 'Manner');
                            

Now when we select the STUDENTS table again, it will look as below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal
8           Jerry       Bann        23          Bonn
9           Meg         Manner      null        null
                            

SqLite Tutorial