Inserting Data

So far, we’ve focused on queries that read data. INSERT is different: it changes the stored data. Now, instead of asking questions from the data, we are asking questions about how to change the data.

• What row do we want to add?
• Which table should that row go into?
• Which columns are we setting?
• How do we verify the change afterward?

Adding a Row

Suppose we want to add a new user.

• Table: users
• Columns we are setting: name, email
• Values: Aino, aino@example.com

Notice that id is not listed here. Pure id columns are typically managed by the DBMS, so we usually let the database choose it for us instead of setting it by hand.

In practice, setting the value of an id column is possible, but it typically would also break the automatic generation of new identifiers. So, it is usually best to let the database handle id values.

Then the INSERT statement can say exactly that:

INSERT INTO users (name, email)
VALUES ('Aino', 'aino@example.com');

This creates one new row in the users table. The new row gets the name value Aino and the email value aino@example.com.

This is a helpful first pattern to read in words:

• INSERT INTO users says which table is changing
• (name, email) says which columns we are filling
• VALUES (...) says which values will be stored in those columns

This explicit column-list style is usually the clearest option. It makes the intended target columns visible and avoids relying on remembered column order.

INSERT INTO users
VALUES (DEFAULT, 'Aino', 'aino@example.com');

INSERT INTO users (name, email)
VALUES ('Leevi', 'leevi@example.com');

Inserting Several Rows

Sometimes the intention is not to add one row, but several similar rows at once.

INSERT INTO users (name, email)
VALUES
  ('Aino', 'aino@example.com'),
  ('Leevi', 'leevi@example.com');

Here each parenthesized value list creates one new row.

This is another place to be careful: each row must match the listed columns in the same order. In this example, the first value goes into name and the second value goes into email for both rows.

Check the Result

A good habit after any write operation is to verify the result instead of only assuming it worked.

One simple way is to follow the insert with a SELECT.

SELECT
  id,
  name,
  email
FROM users
ORDER BY name;

That shows the new table state after the insert.

Including id in this follow-up query is useful because it lets us see the identifier that the DBMS assigned to each new row.

Sometimes a more focused verification query is even clearer. For example, if we only want to check one inserted user, we can filter by email:

SELECT
  id,
  name,
  email
FROM users
WHERE email = 'leevi@example.com';

This is a useful habit because INSERT changes stored data permanently. If you accidentally run the same insert twice, you may create duplicate rows or trigger a constraint error, depending on the table design.

So the safe pattern is:

1. decide what row you want to add
2. write the INSERT with explicit columns
3. run a follow-up SELECT to confirm the result

Check Your Understanding

1. Why is it good practice to name the target columns explicitly in an INSERT statement?
2. What can go wrong if you assume the column order from memory?
3. Why should you inspect the inserted rows after writing them?

SQL Practice

Complete the SQL sequence below to practice inserting rows and verifying the result with follow-up SELECT queries.