# How to insert data into tables using MariaDB INSERT INTO

With MariaDB `INSERT INTO`, you can add new records to an existing table. It’s one of the core SQL commands, used whenever you need to add new content to a database or expand existing information.

## What is the MariaDB `INSERT` Command?

The `INSERT` command in [MariaDB](https://www.ionos.co.uk/digitalguide/server/know-how/what-is-mariadb/) adds new data to a table, **complementing existing data structures** with complete rows. Every time a user registers, submits a form or adds a product, the system executes an `INSERT INTO` operation in the background.

When inserting data, MariaDB checks that values match the **column data types** and that all required fields are filled. If the data violates constraints such as `NOT NULL`, `UNIQUE`, or `FOREIGN KEY`, the database rejects the operation. This means that the table’s structure and the correct column specification are key to using `INSERT` successfully.

## What does the `INSERT INTO` command in MariaDB look like?

The basic syntax for `INSERT INTO` is:

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

- `table_name` specifies the table where the data will be inserted.
- The column list (`column1, column2, ...`) is optional but recommended.
- The `VALUES` clause contains the data for the specified columns.

MariaDB inserts the values in the order the columns are listed. If the number or **order of the values** doesn’t match the column list, it results in an error.

## What parameters and arguments are there for MariaDB `INSERT INTO`?

`INSERT INTO` can be adapted for different purposes. Common variations include:

### Column list

You can insert values into all columns or only into specific ones. If you omit the column list, you need to provide values for every column in the table.

```sql
INSERT INTO users VALUES (1, 'Anna', 'anna@example.co.uk');
```

To insert into selected columns only:

```sql
INSERT INTO users (name, email) VALUES ('Anna', 'anna@example.co.uk');
```

This example populates only the `name` and `email` columns.

### Insert multiple rows

To insert several records efficiently, use multiple value sets in one command:

```sql
INSERT INTO products (name, price)
VALUES ('pen', 1.50),
    ('notepad', 2.00),
    ('bag', 9.90);
```

This approach is faster than running separate `INSERT` statements for each row.

### `INSERT IGNORE`

Using `IGNORE` skips rows that violate constraints and continues inserting the remainder.

```sql
INSERT IGNORE INTO users (id, name) VALUES (1, 'Anna');
```

### `INSERT ... ON DUPLICATE KEY UPDATE`

This form updates existing rows if a unique key conflict occurs:

```sql
INSERT INTO users (id, name)
VALUES (1, 'Anna')
ON DUPLICATE KEY UPDATE name = 'Anna';
```

This approach combines insert and update logic in a single statement. By doing so, it eliminates the need for separate queries. This makes it particularly useful for tasks such as imports, data synchronisation or automated processes.

### Insert with `SELECT`

With `SELECT`, you can copy data from one table into another:

```sql
INSERT INTO archive_orders (id, status)
SELECT id, status FROM orders WHERE status = 'completed';
```

## What are some typical use cases for `INSERT INTO`?

Here are some common use cases for `INSERT INTO`:

### To register users

In this example, MariaDB inserts a new user into the `users` table. The values for `name` and `email` come from a form or application. The `NOW()` value records the registration time of registration.

```sql
INSERT INTO users (name, email, created_at)
VALUES ('Lena', 'lena@example.co.uk', NOW());
```

This method is suitable for traditional web forms like registration forms, where each entry is inserted directly into the database. You just need to ensure `created_at` is of type `DATETIME` or `TIMESTAMP`, so `NOW()` stores the value correctly.

### To insert data from another table

This example shows how to transfer existing data from one table (`orders`) to another (`archive_orders`). The `SELECT` statement retrieves all orders with the status `completed` and passes the columns `id` and `status` to the `INSERT INTO` command.

```sql
INSERT INTO archive_orders (id, status)
SELECT id, status FROM orders WHERE status = 'completed';
```

Such commands are useful for archiving, data cleansing or generating reports. Always ensure that the target table has the same structure – in particular, matching column names, data types and constraints – as the source columns. If they are incompatible, MariaDB will not run the command.


This is a markdown version of: [https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-insert-into/](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-insert-into/) for AI/LLM consumption.