SQLite INSERT INTO

Last update on July 17 2024 11:10:38 (UTC/GMT +8 hours)

Introduction

The INSERT INTO statement in SQLite is used to add new rows of data to a specified table. This command can insert a single row at a time or multiple rows using a single INSERT statement. It is a fundamental part of the SQL Data Manipulation Language (DML), allowing for the creation and population of tables with data.

The INSERT statement supports various conflict resolution algorithms, enabling fine-grained control over how data insertion conflicts are handled. This feature is particularly useful in ensuring data integrity and consistency.

Syntax:

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

Conflict Resolution Algorithms

SQLite provides several conflict resolution algorithms that can be used with the INSERT statement:

OR REPLACE: Replaces the existing row if a conflict occurs.

OR IGNORE: Ignores the new row if a conflict occurs.

OR ABORT: Rolls back the current transaction if a conflict occurs (default behavior).

OR FAIL: Stops the current INSERT statement if a conflict occurs.

OR ROLLBACK: Rolls back the current transaction if a conflict occurs.

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

Insert with Expressions


INSERT INTO prod_mast(prod_id, prod_name, prod_rate, prod_qc)
VALUES (8, 'Biscuit', 50 * 2, 'Good');

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      |
      8|Biscuit  |      100|Good    |

Error Handling

Common errors include:

UNIQUE constraint failed: Occurs when a duplicate value is inserted into a column with a UNIQUE constraint.

NOT NULL constraint failed: Happens when a NULL value is inserted into a column defined with NOT NULL.

Previous: EXISTS Operator
Next: Update