Inserting Data

Until now, we have only read data that was already magically sitting in the database. But how does data get there in the first place?

The portion of SQL used to read data is called DQL (Data Query Language). The portion used to add, modify, or remove data is called DML (Data Manipulation Language).

To add a brand new row to a table, we use the INSERT INTO statement.

The Syntax

An INSERT statement has two main parts:

Identifying the table and the columns you want to fill.
Providing the actual values to put into those columns.

INSERT INTO users (id, name, email) 
VALUES (101, 'Alice Smith', 'alice@example.com');

Let’s break this down:

INSERT INTO users: Specifies the target table.
(id, name, email): This explicitly tells the database which columns we are providing data for, and in what order.
VALUES: The keyword that separates the column definitions from the data.
(101, 'Alice Smith', 'alice@example.com'): The actual data. Notice that text must be wrapped in single quotes, just like in a WHERE clause!

Inserting Multiple Rows

You don’t have to write a separate INSERT INTO statement for every single row. You can insert multiple rows at once by separating the VALUES tuples with commas.

INSERT INTO users (id, name, email) 
VALUES 
  (102, 'Bob Jones', 'bob@example.com'),
  (103, 'Charlie Brown', 'charlie@example.com'),
  (104, 'Diana Prince', 'diana@example.com');

This is much faster and more efficient for the database engine to process than running three separate insert statements.

Omitting Columns

What if you don’t know the user’s email address yet? Or what if the id column is set up to automatically generate numbers (auto-increment)?

You simply leave those columns out of your column list. The database will automatically fill any omitted columns with NULL (or a default value if one was configured when the table was created).

INSERT INTO users (name) 
VALUES ('Evan Wright');

[!WARNING] If a column was defined as NOT NULL when the table was created, and you omit it during an INSERT, the database will throw an error and abort the insertion!

Try adding a new product to our E-Commerce store in the interactive lab!