SQLite - INSERT Query

SQLite INSERT INTO Statement is used to add new rows of data into a table in the database.

Syntax

Following are the two basic syntaxes of INSERT INTO statement.

INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);

Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. However, make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows −

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example

Consider you already have created COMPANY table in your testDB.db as follows −

sqpte> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Now, the following statements would create six records in COMPANY table.

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Paul , 32,  Capfornia , 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Allen , 25,  Texas , 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  Teddy , 23,  Norway , 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Mark , 25,  Rich-Mond  , 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  David , 27,  Texas , 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Kim , 22,  South-Hall , 45000.00 );

You can create a record in COMPANY table using the second syntax as follows −

INSERT INTO COMPANY VALUES (7,  James , 24,  Houston , 10000.00 );

All the above statements would create the following records in COMPANY table. In the next chapter, you will learn how to display all these records from a table.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Capfornia  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Populate One Table Using Another Table

You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate the first table. Here is the syntax −

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

For now, you can skip the above statement. First, let s learn SELECT and WHERE clauses which will be covered in subsequent chapters.