MariaDB Insert Into + Examples

In this MariaDB tutorial, we are going to learn about MariaDB Insert Into statement. Additionally, we will cover the following topics.

• MariaDB Insert Into
• MariaDB Insert Into Multiple Rows
• MariaDB Insert Into Select
• MariaDB Insert Into Select Distinct
• MariaDB Insert Into If Not Exists
• MariaDB Insert Into Table With auto_increment
• MariaDB Insert Into Ignore
• MariaDB Insert Into Table
• MariaDB Insert Into Date
• MariaDB Insert Into Datetime
• MariaDB Insert Into On Duplicate Key Update
• MariaDB Insert Into Temp Table
• MariaDB Insert Into Returning
• MariaDB Insert Into View
• MariaDB Insert Into Multiple Tables
• MariaDB Insert Into Replace

If you do not have a table in your MariaDB database, then first create a table in MariaDB.

MariaDB Insert Into

Inserting data into a table involves the INSERT command in MariaDB. The syntax of the command is usually INSERT followed by the name of the table, fields, and values.

The syntax of the INSERT command is shown below.

INSERT INTO tab_name (field1,field2,...) VALUES (value1, value2,...);

To verify and see the creation of the table, we need to follow the below Syntax.

SHOW COLUMNS FROM tablename;

Parameters or Arguments

• tab_name – The table to insert the records into.
• field1,field2 – The columns in the table to insert values.
• value1, value2 – The values to assign to the columns in the table. So field1 would be assigned the value of value1, field2 would be assigned the value of value2, and so on.

The demonstration of the INSERT command is shown below.

CREATE TABLE USA_library (Book_ID int, Book_Name varchar(20));

INSERT INTO USA_library (Book_ID, Book_Name) 
VALUES (105,'Harry Potter'),
       (106, 'A Game of Thrones'), 
       (109, 'Dune');

In the above example, first, we are using the CREATE TABLE statement to create a table in MariaDB named USA_library. Also, in the table definition, we have defined 2 columns in it. After creating the table, we are using the INSERT INTO statement to insert 3 records in the table.

In the end, to check the table records, we can use the SELECT statement in MariaDB.

Here, we learned about MariaDB Insert Into.

Read: MariaDB create procedure

MariaDB Insert Into Multiple Rows

In this section, we will illustrate how to insert multiple rows into a MariaDB table. And for this implementation, we will discuss an example which is given below.

create table US_dir(
   id int auto_increment,
   name varchar(50) not null,
   dept varchar(50) not null,
   address varchar(50) not null,
   primary key(id));

Here, we created a table i.e. US_dir consisting of three columns(id, name, dept, and address) and id is the primary key(can’t be null).

Here, we need to insert into multiple rows by using the insert command into a statement given below.

Insert into US_dir(name, dept, address)
values (“Jenny”, “Computation”, “London”),
(“Sam”, “Account”, “Calfornia”) ;
select * from US_dir;

Here, we can see that we inserted data into 2 rows into the US_dir table. The result will be as displayed below.

Here, we have learned about MariaDB Insert Into Multiple Rows.

Read: How to Change Column in MariaDB

MariaDB Insert Into Select

Here, we are going to learn about the MariaDB INSERT INTO SELECT statement to insert data into the table where data is fetched through the results of the SELECT statement. It is very useful when we want to copy data from another table.

Syntax of Insert Into Select statement in MariaDB is given below.

INSERT INTO tab_name(column_list)
SELECT 
   select_list 
FROM 
   another_table
WHERE
   condition;

Here, we don’t need to use row values in the value clause, we can use the result of the SELECT statement as the data source for the INSERT statement.

Now, let’s understand the use of the above-specified syntax by executing an example. For this, firstly, we will create 2 new tables using the following SQL code.

CREATE TABLE countrytbl (
	id INT AUTO_INCREMENT PRIMARY KEY,
	country_name VARCHAR(50),
	country_code VARCHAR(50),
	state_name VARCHAR(50)
);
INSERT INTO countrytbl (country_name, country_code, state_name)
VALUES ('United States', 'US', 'Minnesota'),
       ('United States', 'US', 'Arizona'),
       ('New Zealand', 'NZ', NULL),
       ('United States', 'US', 'Alabama'),
       ('Canada', 'CA', 'Alberta'),
       ('Canada', 'CA', 'Québec'),
       ('United States', 'US', 'Ohio'),
       ('United States', 'US', 'Massachusetts'),
       ('New Zealand', 'NZ', NULL),
       ('United States', 'US', 'Hawaii');

CREATE TABLE usa_state_tbl (
	state_id INT AUTO_INCREMENT PRIMARY KEY,
	country_code VARCHAR(50),
	state_name VARCHAR(50)
);

In the above SQL script, first, we are creating a table named countrytbl with 4 different table columns. After this, we are using the INSERT statement to insert rows within the table.

Next, we are using the CREATE TABLE statement again to create another table in MariaDB named usa_state_tbl. Now, let’s use the INSERT INTO SELECT statement to copy some selected rows from countrytbl to usa_state_tbl.

INSERT INTO usa_state_tbl(country_code, state_name)
SELECT countrytbl.country_code, countrytbl.state_name
FROM countrytbl
WHERE countrytbl.country_name = 'United States';

In the above code, we are inserting all the records from the countrytbl where the country name lies in the United States. In the end, we will get the following result.

Also, read: MariaDB Update Statement with Examples

MariaDB Insert Into Select Distinct

In the previous topic, we learned about how to use Insert Into Select but in this, we only need to add the Distinct keyword to eliminate the duplicate records. So, the INSERT INTO statement in MariaDB will be utilized to insert some select records. While the SELECT DISTINCT statement will be utilized to fetch neglect duplicate records.

So, let’s understand how to use the INSERT INTO SELECT DISTINCT statement in MariaDB using an example. And for this, we will be using the following SQL code.

CREATE TABLE usa_states
(
state_id  INT,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 
INSERT INTO usa_states
VALUES (101, 'Alabama', 'AL'),
(101, 'Alabama', 'AL'),
(102, 'Alaska', 'AK'),
(103, 'Arizona', 'AZ'),
(103, 'Arizona', 'AZ'),
(104, 'California', 'CA'),
(105, 'Florida', 'FL'),
(108, 'New Jersey', 'NJ'),
(109, 'New York', 'NY'),
(109, 'New York', 'NY'),
(110, 'Texas', 'TX');

In the above script, we have simply created a table in MariaDB named usa_states. And we have also inserted some data within the table. However, there are some duplicate records in the table.

Next, we are going to create a new table with the same data. But this time, we will take only unique records from the usa_states table.

CREATE TABLE new_state
(
id  INT,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 

INSERT INTO new_state(id, state_name, state_code)
SELECT DISTINCT state_id, state_name, state_code FROM usa_states;

In the above example, we are creating a new table named new_state and for the table data, we are using the SELECT DISTINCT statement. So, in the end, only unique data will be inserted into the new_state table.

Read: How to Remove User in MariaDB

MariaDB Insert Into If Not Exists

In MariaDB, Insert Into If Not Exists is used to insert a record only if it is not present in the table to avoid duplicate records.

Firstly, we need to create a new table or we can use a previously created table, For now, we are using the already created table i.e. US_dir.

If we want to add some records, we can add some records by typing the following command.

Insert into US_dir(name, dept, address)
values (“Jenny”, “Computation”, “London”),
(“Sam”, “Account”, “Calfornia”) ;
select * from US_dir;

Now, we need to add a record with Insert Into If Not Exist. For this, we need to type the following command.

INSERT INTO US_dir(name, dept, address)
SELECT * FROM (SELECT 'Sam', 'Account', 'Calfornia') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM US_dir WHERE name = 'Sam'
) LIMIT 1;

Either it will show a warning or it will add this record.

Here, we have learned about MariaDB Insert Into If Not Exists.

Read: How to Grant All Privileges in MariaDB

MariaDB Insert Into Table With auto_increment

The AUTO_INCREMENT feature in MariaDB can be used to generate columns that can hold succession in numbers. It can be used if we want a column as a primary key in your table.

The syntax for using MariaDB auto_increment is given below.

CREATE TABLE tablename
( 
  column1 datatype NOT NULL AUTO_INCREMENT,
  column2 datatype,
  ...
);

In this, we can assign any column to auto-increment by using auto_increment.

Each column in the table should be labeled as NULL/NOT NULL. If this parameter is changed, the database assumes it to be NULL as the default.

Before we mentioned an example where we used the MariaDB auto_increment.

create table US_dir(
 ->id int auto_increment,
 ->name varchar(50) not null,
 ->dept varchar(50) not null,
 ->address varchar(50) not null,
 ->primary key(id));

Now, we should add some records to the table. To do this, we need to type the following command.

Insert into US_dir(name, dept, address)
values (“Jenny”, “Computation”, “London”),
(“Sam”, “Account”, “Calfornia”) ;
select * from AUS_dir;

Here, we can see that we inserted data into 2 rows AUS_dir table. The result will be as displayed below.

As you can see from the output, we didn’t assign any value to emp_id but it has values in a sequence.

Here, we have learned about MariaDB Insert Into Table With auto_increment.

Read: How to Create View in MariaDB

MariaDB Insert Into Ignore

When an error is encountered, the INSERT command is stopped and it rollback but when IGNORE keyword is used, all the errors will be converted into warnings and will not stop inserts of additional rows.

Example of an error due to which Insert command is stopped.

CREATE TABLE t1 (x INT UNIQUE);

INSERT INTO t1 VALUES(1),(2);

INSERT INTO t1 VALUES(2),(3);

Here, there will be an error because of duplicate entries.

To overcome this error, you can use IGNORE keyword as shown below.

INSERT IGNORE INTO t1 VALUES(2),(3);

Now, there will be a warning and the duplicate entry will be eliminated and the new entry will be added.

To view the warning that occurred above, type the following statement.

SHOW WARNINGS;

The output will be shown below.

Here, we have learned about MariaDB Insert Into Ignore.

Read: Replace Function in MariaDB

MariaDB Insert Into Table

To store records in the table, we use the INSERT command in MariaDB to do this into the selected table.

The syntax for inserting records into the table is given below.

INSERT INTO tabname (field,field2,...) VALUES (value, value2,...);

Moreover, we can also use the below syntax to insert multiple records into the table.

INSERT INTO t1  
(column1, column2, ... )  
VALUES  
(expression1, expression2, ... ),  
(expression1, expression2, ... ),  
...;

Example for the insertion of a single record into the table.

INSERT INTO US_Employee  
(employee_id, employee_name, employee_address, joining_date)  
VALUES(1,'James','California','2019-11-27 07:30:34');  

Example for the insertion of multiple records into the table.

INSERT INTO US_Employee  
(employee_id, employee_name, employee_address, joining_date)  
VALUES    
(2,'Richard','Chicago','2011-09-01 09:20:00'),  
(3,'Katherine','Miami','2017-04-17 03:20:53'),  
(4,'Robert','LA','2006-12-07 11:43:47'),
(5,'Jones','Boston','2009-07-27 10:50:13');

In the above example, we are inserting 4 records into the US_Employee table. Now, to see the data of US_Employee, type the following command.

SELECT * from US_Employee;

After executing the above SELECT statement, we will get the following result.

Here, we have learned about MariaDB Insert Into Table.

Read: How to import CSV files in MariaDB

MariaDB Insert Into Date

In MariaDB, the supported range for Date is ‘1000-01-01‘ to ‘9999-12-31‘ and it is displayed in ‘YYYY-MM-DD‘ format.

To insert the date in the table, firstly, we need to create a table and then we can do the desired thing.

For insertion of the date in the table, we can use the following SQL statement.

INSERT INTO dates VALUES
("2010-01-12"),
("2011-2-28"),
('120314'),
('13*04*21');

SELECT * FROM dates;

Here, you can see that we have entered the date in various formats but the output will be in date format according to the MariaDB convention.

Here, you can see the dates in a proper format after the output has been generated.

Here, we have learned about MariaDB Insert Into Date.

Read: MariaDB Vs SQLite

MariaDB Insert Into Datetime

In MariaDB, the supported range for Datetime is between '1000-01-01 00:00:00.000000‘ and ‘9999-12-31 23:59:59.999999' and it is displayed in ‘YYYY-MM-DD HH:MM: SS.ffffff’ format.

To insert the Datetime in the table, firstly you need to create a table and then you can do the desired thing.

For insertion of the DateTime in the table, you need to write the following command.

INSERT INTO times VALUES 
("2011-03-11"),
("2012-04-19 13:08:22"),
("2013-07-18 13:44:22.123456");

SELECT * FROM times;

Here, you can see that we have entered the date in various formats but the output will be in date format according to the MariaDB convention.

Here, you can see the dates in a proper format after the output has been generated.

Note – You can change the time zone by using the below command.

SET time_zone = 'America/New_York';

Here, you can change the time zone according to you but we have used America/New_York.

Here, we have learned about MariaDB Insert Into Datetime.

Read: MariaDB Timestamp

MariaDB Insert Into On Duplicate Key Update

Duplicate Key Update is the MariaDB/MySQL extension to the insert statement that finds a duplicate unique or primary key that performs UPDATE.

Whenever a new record with an already existing key is inserted, there will always be an error occurred. So, to overcome this you can use Duplicate Key Update.

Here, you can see that there is an error that occurred because of the insertion of a record with a duplicate key.

CREATE TABLE USA_animal (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO USA_animal VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');
INSERT INTO USA_animal VALUES (1,'Antelope');

But when we will use Insert on Duplicate Key Update, there will be no error.

INSERT INTO USA_animal VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope';

To verify the change in the table, type the following command.

SELECT * FROM USA_animal;

From the above result, you can see that Duplicate Key Update has updated the new record corresponding to the same key.

Read: MariaDB Foreign Key + Examples

MariaDB Insert Into Temp Table

So, just like we use INSERT INTO statement in MariaDB to insert some data within the standard database table, we can also use it to insert data in temporary tables. In MariaDB, a temporary table is a special type of database table that enables us to store resultset in temporary form. However, once the current sessions end, the temporary table also gets deleted automatically.

So, in this section, we will discuss an example of how to use the INSERT INTO statement in MariaDB on temporary tables. And the example is as follows.

CREATE TEMPORARY TABLE tmp_usa_states
(
state_id  INT,
state_name VARCHAR(50) NOT NULL,
state_code VARCHAR(2)
);
 
INSERT INTO tmp_usa_states
VALUES (101, 'Alabama', 'AL'),
(102, 'Alaska', 'AK'),
(103, 'Arizona', 'AZ'),
(104, 'California', 'CA'),
(105, 'Florida', 'FL'),
(106, 'Georgia', 'GA'),
(107, 'Montana', 'MT'),
(108, 'New Jersey', 'NJ'),
(109, 'New York', 'NY'),
(110, 'Texas', 'TX');

In the above example, first, we are using the CREATE TEMPORARY TABLE statement to create a temp table named tmp_usa_states. After creating the temp table, we are using the INSERT INTO statement just like we use it with a standard MariaDB table to insert 10 rows of data.

After executing the above example, a new temp table in MariaDB with 10 rows of data will be created. And we can also use the SELECT statement to fetch the resultset of this temp table.

Read: MariaDB Temporary Table + Examples

MariaDB Insert Into Returning

In MariaDB, the INSERT INTO RETURNING statement is utilized to return all the inserted rows in a table. And we can easily use this statement by specifying the RETURNING option with the INSERT INTO statement and also specify the columns that we want to return.

Here is the complete syntax of using the INSERT INTO RETURNING statement in MariaDB.

INSERT INTO tbl_name(col_1, col_2,..) 
VALUES (values_1),(values_2) 
RETURNING col_1, col_2,..;

In the above syntax, we are using the INSERT INTO statement to insert certain values in a specified table. In the end, we are using the RETURNING option and with it, we can specify certain column names whose values we want in return.

Let’s understand the use of the above syntax with the help of an example. And the SQL code for the example is as follows.

CREATE TABLE Department_tbl(  
  dept_no INT AUTO_INCREMENT PRIMARY KEY,  
  dept_name VARCHAR(30),  
  dept_location VARCHAR(30)
);

INSERT INTO Department_tbl(dept_name, dept_location) 
VALUES('RESEARCH','DALLAS'),
      ('OPERATIONS','BOSTON'),
      ('SALES','New York'),
      ('ACCOUNTING','CHICAGO'),
      ('Human Resource Management','Phoenix')
RETURNING dept_no, dept_name, dept_location;

• In the above example, first, we are using the CREATE TABLE statement to create a table named Department_tbl. Within the table definition, we have specified 3 table columns.
• After this, we are using the INSERT INTO statement to insert 5 records in the table.
• In the end, we are using the RETURNING option and specified the name of 3 columns that we want.
• So, when we execute the above SQL script, it will return the data inserted for specified 3 columns.

MariaDB Insert Into View

In this section, we will discuss how to insert data within a MariaDB view using the INSERT INTO statement in MariaDB.

A view in MariaDB is just a SQL query stored within a database with a specific name. Moreover, a view acts like a virtual table that does not hold any actual data. However, we can use the view to perform certain operations on the underlying table. And this also includes using a view to insert some records in the underlying table.

So, let’s understand how to use the INSERT INTO statement to insert some data into a table using view in MariaDB. Here is an example of this implementation.

CREATE VIEW dept_view 
AS 
SELECT dept_no, dept_name, dept_location FROM department_tbl;

In the above SQL code, we are using the CREATE VIEW statement in MariaDB to create a view named dept_view. And within the body of the view, we have defined a SELECT statement that fetches all the rows for specified 3 columns from the department_tbl.

And just like a table, we can use view to fetch data from the underlying table.

Next, we will utilize this view to insert some data within the underlying department_tbl. And the query for this task is as follows.

INSERT INTO dept_view(dept_name, dept_location) 
VALUES('SUPPORT','LOS ANGELES'),
      ('Trianing','AUSTIN');

In the above query, we are using the INSERT INTO statement to insert 2 records within the MariaDB view name dept_view. Now, when we execute the above statement, it will insert the specified records in the underlying department_tbl. And if we query the table directly, we can observe the new records.

MariaDB Insert Into Multiple Tables

Till now, we are trying to insert either single or multiple rows of data using the MariaDB INSERT INTO statement. But, here comes a question can we use this to insert rows with multiple tables.

So, the answer to this question is No. In MariaDB, we cannot use a single INSERT INTO statement to insert data in multiple tables. For this task, we have to use separate INSERT INTO statements for separate tables in MariaDB.

MariaDB Insert Into Replace

In this section, we will discuss how to replace some values of an existing row from a table in MariaDB while using the INSERT INTO statement. So, for this particular task, we can utilize the REPLACE INTO statement in MariaDB.

The REPLACE INTO statement in MariaDB is just like the INSERT INTO statement. However, the REPLACE INTO replaces the values of the existing row based upon the given unique value. Here is the general syntax of using REPLACE INTO statement in MariaDB.

INSERT INTO tab_name (field1,field2,...) 
VALUES (value1, value2,...);

Let’s understand the usability of the above syntax with the help of an example. And for this, the SQL script is as follows.

CREATE TABLE Department_tbl(  
  dept_no INT AUTO_INCREMENT PRIMARY KEY,  
  dept_name VARCHAR(30),  
  dept_location VARCHAR(30)
);

INSERT INTO Department_tbl(dept_name, dept_location) 
VALUES('RESEARCH','DALLAS'),
      ('OPERATIONS','BOSTON'),
      ('SALES','New York'),
      ('ACCOUNTING','CHICAGO'),
      ('Human Resource Management','Phoenix');

In the above example, first, we are using the CREATE TABLE statement to create a table named Department_tbl. Within the table definition, we have specified 3 table columns.
After this, we are using the INSERT INTO statement to insert 5 records in the table.

Next, we will try to replace the location of the SALES department from ‘NEW YORK‘ to ‘LOS ANGELES‘ using the REPLACE INTO statement.

REPLACE INTO Department_tbl(dept_no, dept_name, dept_location) 
VALUES(3, 'SALES','LOS ANGELES');

The above query will first delete the row from the department_tbl where dept_no is defined as 3. And then, inserts the given values in place of it. However, we can also check the result using the SELECT statement.

You may like the following MariaDB tutorials:

• How to Create Function in MariaDB
• MariaDB ERROR 1064
• How to Create Trigger in MariaDB
• How to Drop Column from MariaDB Table
• MariaDB Vs SQL Server
• How to Add Column in MariaDB

Watch my YouTube video for a better understanding of the topic.

In this MariaDB tutorial, we have learned about MariaDB Insert Into. Additionally, we have covered the following topics.

• MariaDB Insert Into
• MariaDB Insert Into Multiple Rows
• MariaDB Insert Into Select
• MariaDB Insert Into Select Distinct
• MariaDB Insert Into If Not Exists
• MariaDB Insert Into Table With auto_increment
• MariaDB Insert Into Ignore
• MariaDB Insert Into Table
• MariaDB Insert Into Date
• MariaDB Insert Into Datetime
• MariaDB Insert Into On Duplicate Key Update
• MariaDB Insert Into Temp Table
• MariaDB Insert Into Returning
• MariaDB Insert Into View
• MariaDB Insert Into Multiple Tables
• MariaDB Insert Into Replace