SQLite INSERT INTO
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