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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook