Once a table has been created, you can populate it with data using the INSERT command.
Here is the syntax:
INSERT INTO table_name (column1, column2) VALUES (value11, value12), (value21, value22), (value31, value32), ...
The first clause after the
INSERT INTO statement specifies the columns which will be part of the insert statement. Each row to insert will specify
the set of columns defined by the first clause, and in the same order. Any other column which was not specified in the first clause will receive
the default value. If a
NOT NULL column was defined in the table and the
INSERT INTO statement missed the column, the
INSERT command will fail
On an INSERT, if the INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually the
next number currently in use in the column. This is true regardless of whether or not the
AUTOINCREMENT keyword is used.
If the columns clause that specifies the list of columns is omitted, then the assumption is that all columns will be provided in the
INSERT INTO table_name VALUES (value1, value2, value3, value4...);
If one of the values is missing then the INSERT statement will simply fail, unless the query can figure out which fields can be set to their default value. In general, this method of inserting is never recommended because the database structure changes the meaning of the query, which can be very dangerous.
Note that it is much much more efficient to insert values in bulk using one query, and not several new
INSERT statments for each row because of the
communication with the database. If you need to take performance into consideration, keep this in mind.
Here are some examples of an
INSERT query in action:
CREATE TABLE customers (first_name NOT NULL, last_name NOT NULL, age); INSERT INTO customers (first_name, last_name, age) VALUES ("John", "Doe", 23); SELECT * FROM customers;
Now let's see what happens if we omit the columns list:
CREATE TABLE customers (first_name NOT NULL, last_name NOT NULL, age); INSERT INTO customers VALUES ("John", "Doe", 23); SELECT * FROM customers;
Let's remove the age - this will cause the query to fail:
CREATE TABLE customers (first_name NOT NULL, last_name NOT NULL, age); INSERT INTO customers VALUES ("John", "Doe"); SELECT * FROM customers;
Let's add more people:
CREATE TABLE customers (first_name NOT NULL, last_name NOT NULL, age); INSERT INTO customers (first_name, last_name, age) VALUES ("John", "Doe", 23), ("Eric", "Smith", 26); SELECT * FROM customers;
Replacing and ignoring
SQLite supports three additional types of syntax to insert data:
INSERT OR REPLACE,
REPLACE INTO and
INSERT OR IGNORE.
REPLACE statement means that if you are inserting an already existing row (meaning, a primary key which already exists in the table) then the
statement will not fail and would actually delete the old row and insert the new one instead.
REPLACE would fail if that row doesn't exist whereas
INSERT OR REPLACE will always work and will either insert a new row or replace an existing one.
INSERT OR IGNORE is similar to
INSERT OR REPLACE but would actually completely ignore the
INSERT command for the specific row that already exists
in the database. This is useful when inserting a bulk of data which some of it (or its primary keys) may already exist.
Insert "John Snow" into the database. John is 33 years old.