SQLite INSERT INTO

Introduction

The INSERT command is used to create new rows in the specified table. The INSERT works on a single table, and can both insert one row at a time or many rows at once using a SELECT command. This statement is a part of the SQL Data Manipulation Language, DML.

Syntax:

INSERT [OR conflict-algorithm]
INTO [database-name .] table-name [(column-list)]
VALUES (value-list)	

Here we will create a new table prod_mast:

CREATE TABLE hrdb.prod_mast(
prod_id integer PRIMARY KEY,
prod_name text(20),
prod_rate integer,
prod_qc text(10) DEFAULT 'OK');
	

Example

The following is the basic INSERT SQL statement. We have specified all column names after the table name and all values after the VALUES keyword.

INSERT INTO prod_mast(prod_id, prod_name, prod_rate, prod_qc)
VALUES(1, 'Pancakes', 75, 'OK');

Here is the inserted row.

sqlite> SELECT * FROM prod_mast;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK

Insert value without PRIMARY KEY column

INSERT INTO prod_mast(prod_name, prod_rate, prod_qc)
VALUES('Gulha', 55, 'Problems');

The above example shows that the prod_id column not been included within the column list. As the prod_id column is defined as INTEGER PRIMARY KEY, so it is auto-incremented by SQLite. So it is clear that the SQLite library adds a new id.

Here is the inserted row.

sqlite> SELECT * FROM prod_mast;
prod_id          prod_name   prod_rate   prod_qc
---------------  ----------  ----------  ----------
1                Pancakes    75          OK
2                Gulha       55          Problems

Insert value without mention column list:

INSERT INTO prod_mast VALUES(3,'Pakora', 48, 'OK');

The above example shows that no column list have been specified after the table name. In such a cases, values for all columns have to supply.

Here is the inserted row.

sqlite> SELECT * FROM prod_mast;
prod_id          prod_name   prod_rate   prod_qc
---------------  ----------  ----------  ----------
1                Pancakes    75          OK
2                Gulha       55          Problems
3                Pakora      48          OK

Insert values for specific columns

Here in the example below, two columns have been specified.

INSERT INTO prod_mast(prod_id, prod_name)
VALUES(4, 'Pizza');

Here is the result after insert.

sqlite> SELECT * FROM prod_mast;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
4                     Pizza                   OK

From the above result, it seems that the value for the two mentioned columns have been inserted and the value for the prod_qc column have also been inserted because for that column the default value assigned 'OK' at the time of creating the table, but the prod_rate column remains empty.

Now use the following command.

sqlite> .nullvalue NULL

The .nullvalue command tells the SQLite to show NULL values as NULL. SQLite shows empty strings for NULL values by default. Now see the result. The empty prod_rate column filled by NULL.

sqlite> SELECT * FROM prod_mast;
prod_id          prod_name   prod_rate   prod_qc
---------------  ----------  ----------  ----------
1                Pancakes    75          OK
2                Gulha       55          Problems
3                Pakora      48          OK
4                Pizza       NULL        OK

Insert duplicate primary key column

If we want to insert such a row in which the value of primary key column already exists, then see the result

sqlite> INSERT INTO prod_mast(prod_id, prod_name, prod_rate, prod_qc)
   ...> VALUES(3, 'Candy', 25, 'OK');
Error: UNIQUE constraint failed: prod_mast.prod_id

UPDATE rows using INSERT

If we want to modify the row for prod_id is 4, we can use the following statement

INSERT OR REPLACE INTO prod_mast(prod_id, prod_name, prod_rate, prod_qc)
VALUES(4, 'Pizza', 200, 'OK');

Here is the result after insert.

sqlite> SELECT * FROM prod_mast;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
4                     Pizza       200         OK

The above result shows that, though the prod_id 4 already exists yet it has been modified by INSERT statement, because the INSERT OR REPLACE statement has been used.

Multi rows insert by a single INSERT statement

INSERT OR REPLACE INTO prod_mast(prod_id, prod_name, prod_rate, prod_qc)
VALUES(5, 'Fudge', 100, 'OK'),
(6, 'Candy', 95, 'Not OK'),
(7, 'Chocolate', 150, 'OK');

Here is the result after insert.

sqlite> SELECT * FROM prod_mast;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
4                     Pizza       200         OK
5                     Fudge       100         OK
6                     Candy       95          Not OK
7                     Chocolate   150         OK

Insert using SELECT statement

Here is another table prod_back

CREATE TABLE hrdb.prod_backup(
prod_id integer PRIMARY KEY,
prod_name text(20),
prod_rate integer,
prod_qc text(10) DEFAULT 'OK');

Here is the statement to insert all rows of prod_mast table rows into pord_backup table.

INSERT INTO prod_backup SELECT * FROM prod_mast;

Here is the result after insert.

sqlite> SELECT * FROM prod_backup;
prod_id               prod_name   prod_rate   prod_qc
--------------------  ----------  ----------  ----------
1                     Pancakes    75          OK
2                     Gulha       55          Problems
3                     Pakora      48          OK
4                     Pizza       200         OK
5                     Fudge       100         OK
6                     Candy       95          Not OK
7                     Chocolate   150         OK

Previous: EXISTS Operator
Next: Update