In this MariaDB tutorial, we will learn about MariaDB Select Into statement by using multiple examples. Additionally, we will cover the following topics.
- MariaDB Select Into
- MariaDB Select Into Table
- MariaDB Select Into Outfile
- MariaDB Select Into Dumpfile
- MariaDB Select Into Temporary Table
- MariaDB Select Into Multiple Variables
- MariaDB Select Into CSV
- MariaDB Select Into Outfile Overwrite
- MariaDB Select Into Outfile Permission Denied
MariaDB Select Into
Here, we will learn about MariaDB Select Into statement, its syntax, and an example.
MariaDB Select Into statement enables selected columns and store them into variables. It should be returning a single row, it warns with an Error-code 1329 if it returns no rows, and error code 1172 occurs if it returns multiple rows.
The statement may display multiple rows in the result set, we can use LIMIT 1 to limit the result set to a single row.
The Syntax of MariaDB Select Into Statement is given below.
SELECT
column1
INTO
@variable
FROM
table_name
WHERE
condition;
The parameters and arguments are given below.
- column1 – Column or expression that we want to select and store into the variables.
- @variable1 – The variable that stores the values from column1, the variables can also be a new table.
- table_name – The table from where we want to get records.
An example illustrating the working of MariaDB Select Into statement.
Here, we are using the Cities table in this example.

SELECT
state_name
INTO
@state
FROM
Cities
WHERE
cname = 'Boston';
In the above example, we are storing data i.e. state_name from the Cities table corresponding to cname = ‘Boston’ value in the @state variable.

This is all about MariaDB Select Into statement.
Also, check: How to Create Table in MariaDB
MariaDB Select Into Table
Here, we will learn about MariaDB Select Into Table, its syntax, and an example.
Select Into Table Statement enables selected columns and stored in a new table. It creates a new table if the table does not exist.
In MariaDB, there is no such thing as SELECT * INTO [table] from old_table instead SELECT INTO [variable] from old_table is only used in MariaDB.
In MariaDB, SELECT * INTO [TABLE] does not work so we have an alternative way to perform this process.
The Syntax of the alternative way to perform the MariaDB Select Into Table Statement is given below.
INSERT INTO
new_table
SELECT *
FROM
table_name;
The parameters and arguments are given below.
- new_table – Newly Created Table in which the selected columns will be stored in MariaDB.
- table_name – table from where we want to get records in MariaDB.
An example illustrating the working of MariaDB Select Into Table statement.
INSERT INTO
USA_Record
SELECT *
FROM
Cities;
Here, we are inserting the rows from the Cities table into the USA_Record table.

This is all about MariaDB Select Into Table statement.
Read: MariaDB Vs SQL Server
MariaDB Select Into Outfile
Here, we will learn about MariaDB Select Into Outfile, its syntax, and an example.
MariaDB Select Into Outfile Statement enables selected columns and stored them in a file. With the use of columns and terminators, we can specify a particular output format.
However, it should return a single row in the result set but the statement may display multiple rows in the result set, we can use LIMIT 1 to limit the result set to a single row.
The Syntax of MariaDB Select Into Outfile Statement is given below.
SELECT
column1, column2, ...
INTO
OUTFILE 'filename'
FROM
table_name
WHERE
condition;
The parameters and arguments are given below.
- column1, column2,… – Columns or expressions that we want to select and store them into the variables in MariaDB.
- filename – File in which the selected columns will be stored in MariaDB.
- table_name – table from where we want to get records in MariaDB.
An example illustrating the working of MariaDB Select Into Outfile statement.
SELECT *
INTO
OUTFILE 'C:\Users\Amar\Desktop\USA_data.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM
Cities
WHERE
cname = 'Miami';
Here, we are storing data from the Cities table to a file i.e. USA_data.txt using terminators and separators.

This is all about MariaDB Select Into Outfile statement.
Read: How to Create Database in MariaDB
MariaDB Select Into Dumpfile
Here, we will learn about MariaDB Select Into Dumpfile, its syntax, and an example.
MariaDB Select Into Dumpfile Statement enables selected columns and dumps them in a file without any terminators and separators, as it does not need to return to the client.
It should return a single row in the result set but the statement may display multiple rows in the result set. This statement may retrieve multiple rows, we can use the LIMIT 1 statement to limit the result set to only a single row.
The Syntax of MariaDB Select Into Dumpfile Statement is given below.
SELECT
column1, column2, ...
INTO
DUMPFILE 'filename'
FROM
table_name
WHERE
condition;
The parameters and arguments are given below.
- column1, column2,… – Columns or expressions that we want to select and store them into the variables in MariaDB.
- filename – File in which the selected columns will be stored in MariaDB.
- table_name – table from where we want to get records in MariaDB.
An example illustrating the working of MariaDB Select Into Outfile statement.
SELECT *
INTO
DUMPFILE 'C:\Users\Amar\Desktop\USA_data.txt'
FROM
Cities
WHERE
cname = 'Houston';
Here, we are dumping data from the Cities table to a file i.e. USA_data.txt.

This is all about MariaDB Select Into Outfile statement.
Read: How to create a user in MariaDB
MariaDB Select Into Temporary Table
Here, we will learn about MariaDB Select Into Temporary Table, its syntax, and an example.
MariaDB Select Into Temporary Table Statement enables selected columns and stores them in a temporary table.
It should return a single row in the result set but the statement may display multiple rows in the result set, we can use LIMIT 1 to limit the result set to a single row.
In MariaDB, SELECT * INTO [TEMPORARY TABLE] does not work so we have an alternative way to perform this process.
The Syntax of the alternative way to perform the MariaDB Select Into Temporary Table Statement is given below.
INSERT INTO
temptable
SELECT *
FROM
table_name;
The parameters and arguments are given below.
- temptable – Temporary Table in which the selected columns will be stored in MariaDB.
- table_name – table from where we want to get records in MariaDB.
An example illustrating the working of MariaDB Select Into Temporary Table statement.
INSERT INTO
place
SELECT *
FROM
Cities;
Here, we are inserting the rows of the Cities table into the place temporary table.

This is all about MariaDB Select Into Temporary Table statement.
Read: How To Check MariaDB Version
MariaDB Select Into Multiple Variables
Here, we will learn about MariaDB Select Into Multiple Variables, its syntax, and an example.
MariaDB Select Into Multiple Variables Statement enables selected columns and stores them into variables.
The syntax of MariaDB Select Into Multiple Variables Statement is given below.
SELECT
column1, column2, ...
INTO
@variable1, @variable2,...
FROM
table_name
WHERE
condition;
The parameters and arguments are given below.
- column1, column2,… – Columns or expressions that we want to select and store them into the variables.
- @variable1, @variable2,… – variables that stores the values from column1, column2,… , the variables can also be a new table.
- table_name – table from where we want to get records.
An example illustrating the working of MariaDB Select Into Multiple Variables statement.
SELECT
cname, state_name, country_name
INTO
@city, @state, @country
FROM
Cities
WHERE
cname = 'Dallas';
In the above example, we are string 3 values into the 3 different variables named @city, @state, and @country respectively. Moreover, we are using the WHERE clause of filter records of the Cities table where the cname is Dallas.

This is all about MariaDB Select Into Multiple Variables statement.
Read: MariaDB DATEDIFF Function
MariaDB Select Into CSV
Here, we will learn about MariaDB Select Into CSV, its syntax, and an example.
MariaDB Select Into CSV Statement enables selected columns and stored them in a CSV file. With the use of columns and terminators, we can specify a particular output format.
It should return a single row in the result set but the statement may display multiple rows in the result set, we can use LIMIT 1 to limit the result set to a single row.
The Syntax of MariaDB Select Into CSV Statement is given below.
SELECT
column1, column2, ...
INTO
OUTFILE 'filename.csv'
FROM
table_name
WHERE
condition;
The parameters and arguments are given below.
- column1, column2,… – Columns or expressions that we want to select and store them into the variables in MariaDB.
- filename.csv – CSV File in which the selected columns will be stored in MariaDB.
- table_name – table from where we want to get records in MariaDB.
An example illustrating the working of MariaDB Select Into CSV statement.
SELECT *
INTO
OUTFILE 'C:\Users\Amar\Desktop\places.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM
Cities
WHERE
cname = 'Tucson';
Here, we are dumping the data of all the columns from the Cities table into a file i.e. places.csv.

This is all about MariaDB Select Into CSV statement.
Read: MariaDB Create Sequence
MariaDB Select Into Outfile Overwrite
Here, we will learn about MariaDB Select Into Outfile Overwrite.
If we try to overwrite a file using Select Into Statement, it shows an error stating “file already exists”. We can delete the file and create it again when needed, but it would be very clear if we use MySQL to do it.
So, it is suggested to create another filename to perform this task.
MariaDB Select Into Outfile Permission Denied
Here, we will learn about MariaDB Select Into Outfile Permission Denied.
This occurs only because of a lack of privileges for a particular user. Apart from MariaDB Root User, no other user is granted privileges even after using the ALL keyword.
We can grant privileges to a user with FILE privileges by using the following command.
grant FILE on *.* to 'James'@'localhost';
Here, we need to give privileges to all the databases using *.* as giving privileges to a particular database does not work.

Here, we learned how to resolve the error MariaDB Select Into Outfile Permission Denied.
You may also like to read the following tutorials.
- MariaDB GROUP BY with Example
- MariaDB AUTO_INCREMENT + Examples
- MariaDB Primary Key With Examples
- MariaDB LIKE Operator [7 Examples]
- MariaDB Set Variable – Complete Guide
- MariaDB Update Statement with Examples
In this tutorial, we have learned about MariaDB Select Into. Additionally, we have covered the following topics.
- MariaDB Select Into
- MariaDB Select Into Table
- MariaDB Select Into Outfile
- MariaDB Select Into Dumpfile
- MariaDB Select Into Temporary Table
- MariaDB Select Into Multiple Variables
- MariaDB Select Into CSV
- MariaDB Select Into Outfile Overwrite
- MariaDB Select Into Outfile Permission Denied
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.