SQLite data insertion (INSERT INTO)

SQLite INSERT INTOThe statement is used to add a new data row to the table in the database.

Syntax

There are two basic syntaxes for the INSERT INTO statement.

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

Here, column1,column2,... columnN is the name of the column in the table where data is to be inserted.

If you need to add values to all columns of the table, it may not be necessary to specify column names in the SQLite query. However, please ensure that the order of the values matches the order of the columns in the table. The SQLite INSERT INTO syntax is as follows-as shown

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

Example

Consider that you have already created the COMPANY table in testDB.db as shown below:

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 create records in the COMPANY table using a second syntax 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. In the next chapter, you will learn how to display all these records from the 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

Fill a table with another table

You can insert data into the table using a SELECT statement from another table, provided that the other table has a set of fields that are necessary to fill the first table. This is the syntax-

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

Now, you can skip the above statements. First, let's learn about the SELECT and WHERE clauses, which will be introduced in subsequent chapters.