SQL INSERT Explained: How to Add Data to a Table the Right Way

• 29 June 2026
• by: Renata Cardoso
• in: MySQL/MariaDB
• Tags: insert into, SQL, sql insert
• note: no comments

If you have ever built a contact form, a product catalog, or a simple user list, then somewhere behind the scenes a database needed to *store* what people typed. That storing happens through one small but mighty SQL command: INSERT. In this guide I want to walk you through it patiently, the way I would if we were sitting side by side, so that by the end you can add data to any table with confidence and avoid the traps that quietly bite beginners.

The SQL INSERT statement does exactly one job, and it does it well: it adds new rows of data to a table. That is the whole idea. You point at a table, you say which columns you want to fill, and you hand over the values. Let’s build that understanding from the ground up.

Key Takeaways
• INSERT adds new rows of data to a table; one statement can add one row or many.
• Always name your columns explicitly: `INSERT INTO table (col1, col2) VALUES (v1, v2)`.
• Strings and dates go in quotes; numbers do not.
• Let `AUTO_INCREMENT` columns assign IDs for you — don’t supply them.
• Use `INSERT … SELECT` to copy rows, and `ON DUPLICATE KEY UPDATE` for upserts.
• The most common errors are column-count mismatches, `NOT NULL` violations, and duplicate keys.

What does the SQL INSERT statement actually do?

Think of a table as a spreadsheet: columns across the top (like `name`, `email`, `created_at`) and rows down the side, one per record. INSERT adds a brand-new row to that grid. It never updates an existing row and never reads data back to you — it simply puts new information in.

Here is the basic shape, the form you should learn first and use most:

“`sql INSERT INTO users (name, email) VALUES (‘Renata Cardoso’, ‘[email protected]’); “`

Run that against a `users` table, and the database replies with something like:

“` Query OK, 1 row affected (0.01 sec) “`

That message — “1 row affected” — is your confirmation that one new row now lives in the table. Read the statement out loud: *insert into users, the columns name and email, the values ‘Renata Cardoso’ and ‘[email protected]’*. The order of the values matches the order of the columns you named. That matching is the heart of every INSERT.

How do you write the basic INSERT INTO syntax?

The `INSERT INTO` syntax has three pieces:

1. `INSERT INTO table_name` — which table receives the row.
2. `(column1, column2, …)` — which columns you are filling.
3. `VALUES (value1, value2, …)` — the data, in the same order as the columns.

“`sql INSERT INTO products (name, price, in_stock) VALUES (‘Wireless Mouse’, 19.99, 1); “`

Notice the quoting carefully, because this trips people up constantly:

• `’Wireless Mouse’` is text, so it goes in single quotes.
• `19.99` is a number, so it has no quotes.
• `1` is also a number (here standing in for “true”), so no quotes.

If you accidentally wrap a number in quotes, MySQL/MariaDB will often forgive you and convert it, but it is a sloppy habit that can cause subtle bugs. Treat the quoting rules as real rules: text and dates get quotes, numbers do not.

Can you insert multiple rows in one statement?

Yes, and you should whenever you have several rows to add — it is faster and cleaner than running INSERT over and over. Just separate each row’s `VALUES` group with a comma:

“`sql INSERT INTO products (name, price, in_stock) VALUES (‘Wireless Mouse’, 19.99, 1), (‘Mechanical Keyboard’, 79.50, 1), (‘USB-C Hub’, 34.00, 0); “`

The result:

“` Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 “`

Three rows in a single trip to the database. This pattern — `VALUES (…),(…),(…)` — is the standard way to bulk-load a handful of records. The column list at the top applies to every row, so each parenthesized group must supply values in that same order.

What happens if you don’t name the columns?

SQL lets you skip the column list entirely:

“`sql INSERT INTO products VALUES (101, ‘Webcam’, 45.00, 1); “`

This *looks* shorter and tidier, but it carries a hidden cost. Without a column list, the database assumes you are supplying a value for every column, in the exact left-to-right order they were defined — including, in this example, the `id` column. You have to know the table’s structure perfectly, by memory, and supply every single value in the right slot.

The INSERT mistake that bites beginners most isn’t a syntax error — it’s the column-less form. `INSERT INTO table VALUES (…)` silently depends on the exact *order* and *count* of the table’s columns. It works fine today. But the moment someone adds, removes, or reorders a column, every column-less INSERT either errors out or, far worse, quietly drops data into the *wrong* columns — an email landing in a phone field, a price landing in a quantity field, with no warning at all. The professional habit that prevents this is simple and non-negotiable: always name your columns. `INSERT INTO users (name, email) VALUES (…)` is immune to schema changes, self-documenting (anyone reading it sees exactly what goes where), and safe even if the table is reorganized later. The terse column-less form saves a few keystrokes and trades them for a fragile dependency on a table layout you can’t even see in the query. Name the columns, every time — it’s the difference between an INSERT that keeps working and one that breaks the day the table changes.

How do partial inserts work — filling only some columns?

You rarely need to fill every column. When you name only some columns, the rest take their default value, or `NULL` if no default exists.

Imagine a `users` table where `created_at` defaults to the current time and `bio` allows `NULL`:

“`sql INSERT INTO users (name, email) VALUES (‘Sofia Lima’, ‘[email protected]’); “`

The database fills in the rest automatically:

“` +—-+————-+———————+———————+——+

| id | name | email | created_at | bio | +—-+————-+———————+———————+——+

| 7 | Sofia Lima | [email protected] | 2026-06-29 14:02:11 | NULL | +—-+————-+———————+———————+——+ “`

This is exactly why naming columns is so freeing: you only mention what you care about, and the database handles defaults gracefully. Just be aware — if a column is defined `NOT NULL` and has no default, you must supply it, or the insert fails. We’ll see that error shortly.

How does AUTO_INCREMENT handle IDs for you?

Most tables have a primary key like `id` that should be unique for every row. Typing those numbers by hand is error-prone. That is what `AUTO_INCREMENT` solves: the database assigns the next number automatically.

The golden rule is don’t supply the AUTO_INCREMENT value yourself. Just leave that column out of your column list:

“`sql INSERT INTO users (name, email) VALUES (‘Mateo Ríos’, ‘[email protected]’); “`

“` Query OK, 1 row affected (0.00 sec) “`

The database picks the next available `id` — say `8` — and after the insert you can ask what it chose:

“`sql SELECT LAST_INSERT_ID(); “`

“` +——————+

| LAST_INSERT_ID() | +——————+

| 8 | +——————+ “`

This is invaluable when you need the new ID to link related records, like attaching an order to the customer you just created.

How do you copy rows with INSERT … SELECT?

Sometimes the data you want to insert already lives in another table. Instead of reading it out and typing it back in, you can feed a `SELECT` directly into an `INSERT`:

“`sql INSERT INTO archived_users (name, email) SELECT name, email FROM users WHERE created_at < '2025-01-01'; ```

“` Query OK, 12 rows affected (0.02 sec) Records: 12 Duplicates: 0 Warnings: 0 “`

Every row the `SELECT` returns becomes a new row in `archived_users`. The columns you list after `INSERT INTO` must line up with the columns the `SELECT` produces, in order. This one statement copied twelve old accounts into the archive without a single typed value. If you want to go deeper on the SELECT side of this, see the linked guide below on retrieving data.

How do you handle duplicates with INSERT IGNORE and upserts?

What if you try to insert a row whose unique key already exists — say an email that’s already registered? By default, the insert fails with a duplicate-key error. SQL gives you two graceful ways to handle this.

`INSERT IGNORE` quietly skips rows that would violate a unique constraint instead of erroring:

“`sql INSERT IGNORE INTO users (name, email) VALUES (‘Duplicate Person’, ‘[email protected]’); “`

“` Query OK, 0 rows affected, 1 warning (0.00 sec) “`

Zero rows affected — the existing `[email protected]` row was left untouched, no error raised.

`ON DUPLICATE KEY UPDATE` — often called an upsert (update + insert) — inserts the row if it’s new, or updates the existing one if the key already exists:

“`sql INSERT INTO users (name, email) VALUES (‘Sofia L.’, ‘[email protected]’) ON DUPLICATE KEY UPDATE name = VALUES(name); “`

“` Query OK, 2 rows affected (0.01 sec) “`

Here the existing row’s `name` was updated to `’Sofia L.’` because the email matched. (The “2 rows affected” is MySQL’s way of reporting an update-via-insert.) Upserts are perfect for “save this record, whether it exists yet or not.”

What are the different forms of INSERT, and when do you use each?

Here is a quick reference you can come back to:

INSERT form	What it does	When to use
`INSERT INTO t (cols) VALUES (…)`	Adds one row, columns named	Default choice — use this almost always
`INSERT INTO t (cols) VALUES (…),(…)`	Adds many rows at once	Bulk-loading several known rows
`INSERT INTO t VALUES (…)`	Adds one row, no column names	Avoid — fragile against schema changes
`INSERT INTO t (cols) SELECT …`	Copies rows from another query	Archiving, duplicating, migrating data
`INSERT IGNORE INTO t …`	Skips rows that violate unique keys	Tolerating known duplicates safely
`INSERT … ON DUPLICATE KEY UPDATE`	Inserts or updates (upsert)	“Save it whether or not it exists”

And a small reference for data types and quoting, since this is where so many first inserts go wrong:

Data type	Example value in SQL	Quoted?
Text / string	`’Wireless Mouse’`	Yes, single quotes
Number (integer)	`42`	No
Number (decimal)	`19.99`	No
Date / datetime	`’2026-06-29 14:02:11’`	Yes, single quotes
Boolean (as 0/1)	`1`	No
Unknown / empty	`NULL`	No (keyword, not a string)

DarazHost: practice SQL INSERT without fear

Learning INSERT is so much easier when you have a safe place to experiment. DarazHost hosting includes MySQL/MariaDB databases with phpMyAdmin built in, so you can write and run `INSERT` — and every other SQL statement — through a friendly visual interface that shows you results instantly. Fast SSD storage keeps your inserts and queries quick even as your tables grow, and automatic backups mean you can add, tweak, and experiment with data worry-free, knowing a clean copy is always there if you need it. You get the database tools to learn and use SQL with genuine confidence, backed by 24/7 support whenever you get stuck. It’s the kind of environment I wish I’d had when I was first running my own `INSERT INTO` statements.

What are the most common INSERT errors, and how do you fix them?

Errors are not failures — they’re the database telling you precisely what to fix. Here are the three you’ll meet most.

1. Column count doesn’t match value count. You named three columns but gave two values (or vice versa):

“`sql INSERT INTO products (name, price, in_stock) VALUES (‘Webcam’, 45.00); “`

“` ERROR 1136 (21S01): Column count doesn’t match value count at row 1 “`

*Fix:* count your columns and your values — they must be equal. This is one more reason naming columns helps: the mismatch is visible right in the statement.

2. NOT NULL violation. You skipped a column that is required and has no default:

“`sql INSERT INTO users (name) VALUES (‘No Email Person’); “`

“` ERROR 1364 (HY000): Field ’email’ doesn’t have a default value “`

*Fix:* supply the required column, or give that column a default in the table definition.

3. Duplicate key. You inserted a value into a unique column that already exists:

“`sql INSERT INTO users (name, email) VALUES (‘Someone’, ‘[email protected]’); “`

“` ERROR 1062 (23000): Duplicate entry ‘[email protected]’ for key ’email’ “`

*Fix:* use a different value, or reach for `INSERT IGNORE` or `ON DUPLICATE KEY UPDATE` if duplicates are expected.

How do you keep your INSERTs safe and correct?

A few small habits keep your data clean for years:

• Match columns to values, every time — same order, same count.
• Always name your columns so schema changes can’t silently corrupt your data.
• Respect constraints — `NOT NULL`, `UNIQUE`, and foreign keys exist to protect your data; work with them, not around them.
• Quote text and dates, never numbers.
• Let `AUTO_INCREMENT` do its job — don’t hand-pick IDs.

For the bigger picture of how INSERT fits alongside reading, updating, and managing your data, see our complete guide to MySQL and MariaDB databases for website owners — it ties all of these skills together.

Frequently asked questions about SQL INSERT

Do I have to list the column names in an INSERT? Technically no — SQL allows `INSERT INTO table VALUES (…)` without them. But you should list them anyway. Naming columns makes your insert immune to schema changes, self-documenting, and far less error-prone. It is the single best habit you can build with INSERT.

Can one INSERT statement add more than one row? Yes. Separate each row’s values with a comma: `VALUES (…),(…),(…)`. This is faster than running many separate inserts and is the standard way to add several known rows at once.

What’s the difference between INSERT and UPDATE? INSERT adds a brand-new row to a table. UPDATE changes values in rows that already exist. If you want “insert if new, update if it exists,” that combination is called an upsert — use `ON DUPLICATE KEY UPDATE`.

Why do some values have quotes and others don’t? Text (strings) and dates must be wrapped in single quotes so the database treats them as literal values. Numbers are written without quotes. `NULL` is a special keyword and is never quoted.

How do I find the ID a new row was given by AUTO_INCREMENT? Run `SELECT LAST_INSERT_ID();` immediately after your insert. It returns the ID the database just assigned, which you can then use to link related records.