Add Data To Table MariaDB

The Add Data To Table MariaDB statement simply utilizes an INSERT INTO statement from the MariaDB Server, and data is inserted into the table. And this is what this MariaDB tutorial will discuss. We will discuss and conclude several scenarios to help you better comprehend the subject. Below is a list of every topic we’ll cover.

  • Add Data To Table MariaDB
  • How To add Data in a Table in MariaDB
  • Adding Data to Table MariaDB
  • Insert Data Into Table in MariaDB
  • Add Data To Table MariaDB Not Working
  • Add Data To Table MariaDB Select
  • Add Data To Table MariaDB Temporary

Add Data To Table MariaDB

Here we will learn and understand how to add data to a table in the current database by the query, which will be explained with the help of an illustrated example and syntax.

In MariaDB, the INSERT INTO statement is used to add a new row to the table by the query. Here is the syntax of the MariaDB INSERT INTO statement by the following query:

SYNTAX:

INSERT INTO YOUR_TABLE_NAME (COLUMN_1,COLUMN_2,COLUMN_N)
VALUES( VALUE_1,VALUE_2,VALUE_N);

In this syntax explanation:

  • First, we need to specify the name of the table in which we want to insert data after using the INSERT INTO statement.
  • Second, we will specify the list of the column name of the table within parenthesis after the YOUR_TABLE_NAME.
  • Third, we will specify the values of the parenthesis after using the VALUES keyword. The number of values should be named to the number of specified columns. And the column list and the values should be in the same order.

As we know that the column_list of the table is optional but it is good practice for programming. If you have skipped the column_name of the table column list then we are sure that we have omitted the column as the default value otherwise it will throw an error.

Here is a sample example of the MariaDB INSERT INTO statement by the following query:

EXAMPLE:

INSERT INTO STATES_OF_USA(STATE_NAME,STATE_SHORTFORM,STATE_POPULATION)
VALUES('WYCOMING','WY','LOW');

SELECT * FROM STATES_OF_USA
WHERE STATE_ID=50;

As we see in the above query, we have inserted a new record into the STATES_OF_USA table by using the INSERT INTO statement. If we want to check the last inserted record of the STATES_OF_USA table then we have used the LAST_INSERTED_ID function or we can use the SELECT statement with the WHERE condition by the query. Here is the execution of the SELECT statement says that:

  • In the SELECT statement, we retrieve all records from the STATES_OF_USA table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 50 from the STATES_OF_USA table.
  • If the SELECT statement retrieve all records from the STATES_OF_USA table only when the WHERE condition turns out to be TRUE. But if the SELECT statement retrieves an empty record set from the STATES_OF_USA table and is executed successfully only when the WHERE condition gets a FALSE value.
Adding data to table MariaDB example
Example of Add Data To Table MariaDB

We hope that you have understood the subtopic “Add Data To Table MariaDB” by using the MariaDB INSERT INTO statement on the table by the query. For a better understanding, we have used an example and explained it in depth.

Also, read: MariaDB See If Table Exists

How To add Data in a Table in MariaDB

We will learn and understand how to add data to a table in the MariaDB Server by the query, which will be explained with the help of a sample example.

EXAMPLE:

INSERT INTO JOHNS_HOPKINS_HOSPITAL(PATIENT_FIRSTNAME,PATIENT_LASTNAME,PATIENT_EMAIL,
GENDER,PATIENT_ADMITDATE,PATIENT_DISCHARGEDATE)
VALUES('Chris','Brown','brown.chirs@hollywood.com','Male','2021-04-08 12:45:12','2022-05-03 14:12:56');

SELECT LAST_INSERT_ID();

In the first query, we have inserted a new record into the JOHNS_HOPKINS_HOSPITAL table by using the INSERT INTO statement. If we want to check the last record of the JOHNS_HOPKINS_HOSPITAL table then we will use the LAST_INSERTED_ID function by the second query.

Example of how to add data in a table in MariaDB
Example of How to Add Data in a Table in MariaDB

We hope that you have understood the subtopic “How To add Data in a Table in MariaDB” by using the MariaDB INSERT INTO statement on the table by the query. We have used a sample example and described it in deepness, for a better explanation.

Read: MariaDB Add Column With Default Value

Insert Data Into Table in MariaDB

Here we will learn and understand how to insert data into the existing table of the MariaDB Server by the query, which will be explained with the help of a sample example.

EXAMPLE:

INSERT INTO WALMART_CUSTOMER 
VALUES(21,'Johnny','Walker',89,'1987-05-01','21.145.32.1');

SELECT * FROM WALMART_CUSTOMER
WHERE ID>=20;

In the query example explanation:

  • First, we have inserted a new record into the WALMART_CUSTOMER table by using the INSERT INTO statement.
  • Second, we have used the SELECT statement to retrieve all records from the WALMART_CUSTOMER table with the WHERE condition. In the WHERE condition, the ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the WALMART_CUSTOMER table.
    • If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the WALMART_CUSTOMER table otherwise vice-versa.
  • The main purpose of using the SELECT statement in the WALMART_CUSTOMER table because to check the INSERT INTO statement has inserted a new record into the WALMART_CUSTOMER table or not.
Example of insert data into table in MariaDB
Example of Insert Data Into Table in MariaDB

We hope that you have understood the subtopic “Insert Data Into Table in MariaDB” by using the MariaDB INSERT INTO SELECT statement on the existing table by the query. We have used a sample example and defined it in deepness, for better understanding.

Read: How to Show Tables in MariaDB

Add Data To Table MariaDB Not Working

In this MariaDB subtopic tutorial, we will learn and understand that adding data to a table is not working in the MariaDB Server, which will be explained with the help of a sample example.

ERROR EXAMPLE:

INSERT INTO WALMART_CUSTOMER 
VALUES(21,['Johnny]' , ['Walker'] ,89,'1987-05-01','21.145.32.1');

As we see in the above query, we have used the INSERT INTO statement to insert a new record into the WALMART_CUSTOMER table but it throws an error because we can’t use the [] signs in the value_list while entering the record into the table.

Here the syntax says “SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘[‘Johnny’],[‘Walker’],89,’1987-05-01′, ‘21.145.32.1’)’ at line 2”.

Example of Add Data To Table MariaDB Not Working

SYNTAX REMOVE EXAMPLE:

INSERT INTO WALMART_CUSTOMER 
VALUES(21,'Johnny','Walker',89,'1987-05-01','21.145.32.1');

SELECT * FROM WALMART_CUSTOMER
WHERE ID>=20;

In the syntax explanation:

  • In the first query, we inserted a new record into the WALMART_CUSTOMER table by using the INSERT INTO statement.
  • In the second query, we have then used the SELECT statement to retrieve all records from the WALMART_CUSTOMER table with the WHERE condition.
    • In the WHERE condition, the ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the WALMART_CUSTOMER table.
    • If the SELECT statement retrieves all records from the WALMART_CUSTOMER table only when the WHERE condition turns out to be TRUE otherwise vice-versa.
  • The main purpose of using the SELECT statement with the WHERE condition on the WALMART_CUSTOMER table is to check whether new records have been inserted into the WALMART_CUSTOMER table or not.

We hope that you have understood the subtopic “Add Data To Table MariaDB Not Working” by using the MariaDB INSERT INTO statement on the table by the query. For a better understanding, we have also shown the two examples and explained them in depth.

Read: MariaDB Alter Table Add Index

Add Data To Table MariaDB Select

We will learn and understand how to add data to a table in MariaDB by using the INSERT INTO SELECT statement on the table by the query, which will be explained with the help of a sample example.

The value list of an INSERT statement can be either literal values or the result set of a query. Here is the syntax of the MariaDB INSERT INTO SELECT statement by the following query:

SYNTAX:

INSERT INTO NEW_TABLENAME (COLUMN_LIST )
SELECT EXPRESSION FROM YOUR_TABLENAME
WHERE [ CONDITIONS ] ;

In this syntax explanation:

  • First, we need to specify the table_name and a list of columns that we want to insert data by the query.
  • Second, specify the SELECT statement that returns a result set whose columns are corresponding to the column_list.

The MariaDB INSERT INTO SELECT statement is used to copy data from another table and insert a record into another table.

EXAMPLE:

CREATE TABLE USA_STATES(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY,
STATE_NAME VARCHAR(50) UNIQUE,
STATE_SHORTFORM VARCHAR(10),
STATE_POPULATION ENUM('HIGH','MEDIUM','LOW'),
STATE_SIZE VARCHAR(10) DEFAULT '25M');

INSERT INTO USA_STATES(STATE_ID,STATE_NAME,STATE_SHORTFORM,STATE_POPULATION,STATE_SIZE)
SELECT * FROM states_of_usa
WHERE STATE_ID>=10;

SELECT * FROM USA_STATES;

In the query explanation:

  • In the first query, we created another table called USA_STATES by using the CREATE TABLE statement. The main purpose is that all records of the STATES_OF_USA table will be transferred to the USA_STATES table which will be done by using the INSERT INTO SELECT statement.
  • In the second query, we have used the INSERT INTO SELECT statement which will insert all record from the STATES_OF_USA table with the column_list which are used inside the USA_STATES table.
    • The SELECT statement will retrieve all records from the USA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 10 from the STATES_OF_USA table.
    • If the WHERE condition gets a TRUE value then the SELECT statement will retrieve all records from the STATES_OF_USA table. But if the SELECT statement gets executed successfully and retrieves an empty record set from the STATES_OF_USA table only when the WHERE condition gets a FALSE value.
  • In the third query, we used the SELECT statement once again to retrieve all records from the USA_STATES table. The main purpose of the SELECT statement using again is that the INSERT INTO SELECT statement will insert and transfer all records from the STATES_OF_USA table to the USA_STATES table.
Example of add data to table MariaDB select
Example of Add Data To Table MariaDB Select

We hope that you have understood the subtopic “Add Data To Table MariaDB Select” by using the MariaDB INSERT INTO SELECT statement on the current table to another table by the query. We have used an example and explained it in depth, for better explanation.

Read: MariaDB Add Auto Increment Column

Add Data To Table MariaDB Temporary

Here we will learn and understand how to add data in a temporary table from the existing table by the query, which will be explained with the help of a sample example.

EXAMPLE:

CREATE TEMPORARY TABLE HOSPITALOF_USA
SELECT * FROM JOHNS_HOPKINS_HOSPITAL
WHERE PATIENT_ID>=1;

SELECT * FROM HOSPITALOF_USA;

INSERT INTO HOSPITALOF_USA (PATIENT_ID,PATIENT_FIRSTNAME,PATIENT_LASTNAME,PATIENT_EMAIL,GENDER,
PATIENT_ADMITDATE,PATIENT_DISCHARGEDATE)
VALUES(52,'Michael','Jordan','jordan.michael@nba.com','Male','2021-04-08 21:54:10','2022-06-19 04:15:36');

SELECT * FROM HOSPITALOF_USA
WHERE PATIENT_ID>51;

In the query explanation:

  • In the first query, we have created a temporary table called HOSPITALOF_USA from the existing table as the JOHNS_HOPKINS_HOSPITAL table by using the CREATE TEMPORARY TABLE statement.
  • In the second query, we have used the SELECT statement to check and retrieve all records from the HOSPITALOF_USA temporary table.
  • In the third query, we have inserted a new record into the HOSPITALOF_USA temporary table by using the INSERT INTO statement.
  • In the last query, we used the SELECT statement to retrieve all records from the HOSPITALOF_USA temporary table with the WHERE condition. In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN operator to find a value greater than 51 from the HOSPITALOF_USA temporary table.
    • If the SELECT statement retrieves all records from the HOSPITALOF_USA temporary table only when the WHERE condition turns out to be TRUE. But if the WHERE condition turns out to be a FALSE value then the SELECT statement will be executed successfully and will retrieve an empty record set from the HOSPITALOF_USA temporary table.
Example of add data to table MariaDB temporary
Example of Add Data To Table MariaDB Temporary

We hope that you have understood the subtopic “Add Data To Table MariaDB Temporary” by using the MariaDB CREATE TEMPORARY TABLE and INSERT INTO statement on the table by the query. For a better experience, we used an illustration and demonstrated it in deepness.

Also, take a look at some more MariaDB tutorials.

Here in this tutorial, we understood the use of Add Data To Table MariaDB statements after reading this lesson. Moreover, we have also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • Add Data To Table MariaDB
  • How To add Data in a Table in MariaDB
  • Adding Data to Table MariaDB
  • Insert Data Into Table in MariaDB
  • Add Data To Table MariaDB Not Working
  • Add Data To Table MariaDB Select
  • Add Data To Table MariaDB Temporary