SQLite INSERT Statement

SQLite'sINSERT INTOstatement is used to add new data rows to a table in the database.

Syntax

The INSERT INTO statement has two basic syntaxes, as follows:

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.

If you want to add values for all columns of the table, you do not need to specify column names in the SQLite query. However, make sure the order of the values is the same as the order of the columns in the table. The SQLite INSERT INTO syntax is as follows:

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

Examples

Suppose you have created the COMPANY table in testDB.db as follows:

sqlite> 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 will create six records in the COMPANY table:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 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 also use the second syntax to create a record in the COMPANY table, as shown below:

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

All the above statements will create the following records in the COMPANY table. The next chapter will teach you how to display all these records from a table.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  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

Use One Table to Populate Another Table

You can populate data into another table by using a SELECT statement on a table with a set of fields. The syntax is as follows:

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

You can skip the above statements for now and first learn the SELECT and WHERE clauses introduced in later chapters.