The INSERT statement is used to add new rows to a table.
Remember that each example uses its own copy of the tables. If you want to see the current state of a table after an INSERT, edit the example to query the contents of the table, e.g.: SELECT * FROM users;
Alternatively, see the bottom of this topic for an example of the RETURNING clause used to return the contents of an INSERT.
The "reset" button will revert any changes to the table in that example and re-run the original statement from the example.
Depending on the database engine and SQL client you are using, running an INSERT typically returns the number of rows that were inserted. This example shows that this INSERT statement inserted one row into the database. Running the example again will insert another row.
You may have noticed that we omitted the id column from the INSERT statement. This is because we are taking advantage of a SQLite feature where the column specified as the PRIMARY KEY ( id in this case) will be automatically incremented for new rows if we omit it from our INSERT.
"Auto-incrementing" columns are featured in most other relational databases too but may have different syntax or require special annotation in your CREATE TABLE statement. See your database's documentation regarding primary keys and auto-incrementing data types.
This example uses datetime('now')for the created_at column, which is an example of a function which returns the current day and time.
You can omit the list of column names and only specify the values to insert. The values must then match their columns in order from left to right.
While this is valid SQL, it is considered best practice to include the list of column names in an INSERT. Specifying column names makes a statement more readable and more explicit in case the structure of the table changes later.
Notice that we also need to specify the id here whereas the previous example did not require it. If you try to run this example twice the database will return an error. This is because we are trying to insert a non-unique value into a column that we specified as our PRIMARY KEY, which requires a unique value for every row. Uniqueness is a common constraint in relational databases.
INSERT INTO users
VALUES (9, 'Joshua', 'Seti', 54, 'josh@st.co', datetime('now'));
1 row affected.
Columns can have default values that you specify at the time of table creation. If you omit a column in an INSERT and no default values are specified, a NULL value is inserted for that column. If the column disallows NULL values, the database will return an error.
If the column allows it you can use the keyword NULL to explicitly insert a null value.
Notice how newly created rows have a created_at that contains the current date even if you don't explicitly specify a value. This is because our users table was created using a CREATE TABLE statement that specifies a default value of datetime('now')for the created_at column.
INSERT INTO users (name_first, name_last)
VALUES ('Joshua', NULL);
1 row affected.
You can insert the results of a SELECT into a table in a single statement.
This technique can be used to migrate data from one table to another or aggregate data from multiple different tables into one.
INSERT INTO users
(name_first, name_last, age, email_address, created_at)
SELECT
name_first, name_last, age, email_address, created_at
FROM users;
8 rows affected.
If you want to insert a row that consists only of default values, you can use the DEFAULT VALUES clause. This is a lesser-known feature but can be useful in cases where you want to create rows ahead of time and update them later.
INSERT INTO users
DEFAULT VALUES;
1 row affected.
You can configure a conflict resolution action in case your INSERT fails because of a constraint violation. We saw an example of a constraint violation above when we tried to insert a row with a non-unique id column.
There are several different conflict resolutions you can apply. See your database's documentation for its supported algorithms. This example uses REPLACE, which will allow the INSERT to proceed and replace the row that it is in conflict with.
Some database engines use alternative syntax such as INSERT ... ON CONFLICT ...for the same feature.
INSERT OR REPLACE INTO users
(id, name_first, name_last, age, email_address, created_at)
VALUES (1, 'Joshua', 'Seti', 54, 'josh@st.co', datetime('now'));
1 row affected.
Some database engines support the RETURNING clause, which will cause the database to return the rows that were inserted. You can specify column names to return or * to include all columns.