MariaDB Select Into + Examples

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