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 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:

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.

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

To insert into selected columns only:

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:

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.

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:

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:

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.

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.

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.