Basics of Inserting Data Using INSERT INTO

What is data insertion?

Imagine you just opened a university. You have a database with tables, for example, students to store info about students. Now it's time to announce which students got in and add their data to the table. In SQL, you use the INSERT INTO command for this. It lets you add a new row to a table—like filling in a new row in Excel by hand, but way faster, more powerful, and with code!

Adding data isn't just for basic stuff. It's used everywhere—from recording orders in an online store to storing info about transactions in a bank. And for universities, it's student lists, teachers, and even schedules.

Basic syntax of the INSERT INTO command

In SQL, the INSERT INTO command is a simple way to add a new row to a table. Let's check out the basic syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Component breakdown:

1. table_name — the name of the table you're adding data to.
2. (column1, column2, column3, ...) — the list of columns you're providing values for.
3. VALUES (value1, value2, value3, ...) — the list of values you want to insert into the corresponding columns.

Example: let's add a new student to the students table. Suppose we have this table:

Now let's add a second student using INSERT INTO:

INSERT INTO students (id, name, age, course)
VALUES (2, 'Anna Lin', 19, 'Computer Science');

After running the query, the table will look like this:

Inserting data into all columns

If you want to insert values for all columns in the table, you can skip the column names in the INSERT INTO command. This only works if you provide values for every column in the right order.

Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example: let's say you're adding another student to the students table:

INSERT INTO students
VALUES (3, 'Dan Sim', 20, 'Physics');

Result:

But this approach can be risky if the table structure changes. So, it's recommended to always specify the column list to avoid mistakes.

Practical example: adding a student

Let's try adding a new student to our students table, using the full power of the INSERT INTO command.

Step 1: Check current data

Before you start, it's useful to check what data is already in the table. Let's use the SELECT command:

SELECT * FROM students;

Step 2: Add a new student

Now let's add student Peter Chi, who's studying "Biology" and is 21 years old:

INSERT INTO students (id, name, age, course)
VALUES (4, 'Peter Chi', 21, 'Biology');

Step 3: Make sure the data was added

Let's run the select query again:

SELECT * FROM students;

Result:

Pretty cool, right? Now Peter is added to our university.

Common mistakes when using INSERT INTO

Mismatch between column list and values. If you specify, say, 4 columns but try to insert only 3 values, SQL will throw an error:

INSERT INTO students (id, name, age)
VALUES (5, 'Eva Green', 22, 'Chemistry');

Error: number of columns doesn't match number of values.

Constraint violations. If a column requires uniqueness (UNIQUE) or is mandatory NOT NULL, breaking these rules will cause a failure.

Data types.

If you try to insert text into a numeric field, PostgreSQL won't be happy either:

INSERT INTO students (id, name, age, course)
VALUES (5, 'Axel Linz', 'twenty', 'Chemistry');

Why do you need this in real projects?

Knowing the INSERT INTO command is a skill you'll use in pretty much every app that works with databases. In real life, you'll use this command for:

• Registering new users in a system.
• Adding orders in online stores.
• Saving payment data in payment systems.
• Updating daily data about students, attendance, and grades in educational systems.

Every time your apps interact with users or external data sources, the INSERT INTO command becomes your main tool for writing that data into the database.

If you're working with backend apps or planning to build APIs for data processing, being able to insert data with SQL is a basic but super important skill!