MySQL Selecting data into a file and table

In this article, we will explore how to select data from a MySQL database and save it into a file or table. The SELECT statement is one of the most commonly used statements in MySQL and is used to retrieve data from one or more tables in a database. There are several options for exporting and importing data in MySQL, including using the SELECT INTO OUTFILE and LOAD DATA INFILE statements.

Saving Data into a File

The SELECT INTO OUTFILE statement is used to save the results of a SELECT statement into a file on the server. This statement can be very useful if you need to save the data for later use or if you need to import the data into another database.

The syntax for SELECT INTO OUTFILE is as follows:

SELECT column_name1, column_name2, ... INTO OUTFILE 'file_name' FROM table_name;

Where “file_name” is the name of the file you want to create, and “table_name” is the name of the table you want to select data from.

Example:

Let’s consider a database named “employee_db” and a table named “employees” in it. The employees table contains the following data:

+----+--------+--------+--------+ 
| id | first | last | salary | 
+----+--------+--------+--------+ 
| 1 | John | Doe | 50000 | 
| 2 | Jane | Doe | 55000 | 
| 3 | Jim | Smith | 60000 | 
| 4 | Sarah | Johnson| 65000 | 
+----+--------+--------+--------+

Now, let’s say we want to save the data from the employees table into a file named “employee_data.txt”. We can use the following SELECT INTO OUTFILE statement to do this:

SELECT * INTO OUTFILE '/tmp/employee_data.txt' FROM employees;

This statement will create a file named “employee_data.txt” in the “/tmp” directory and will contain the following data:

1,John,Doe,50000 
2,Jane,Doe,55000 
3,Jim,Smith,60000 
4,Sarah,Johnson,65000

Saving Data into a Table

Another option for saving the results of a SELECT statement is to insert the data into a new table. This can be done using the SELECT INTO statement. The syntax for the SELECT INTO statement is as follows:

SELECT column_name1, column_name2, ... INTO new_table_name FROM table_name;

Where “new_table_name” is the name of the new table you want to create, and “table_name” is the name of the table you want to select data from.

Example:

Let’s consider the same employees table as in the previous example. Now, let’s say we want to create a new table named “employee_data” and insert the data from the employees table into it. We can use the following SELECT INTO statement to do this:

SELECT * INTO employee_data FROM employees;

This statement will create a new table named “employee_data” and will contain the same data as the employees table:

+----+--------+--------+--------+ 
| id | first | last | salary | 
+----+--------+--------+--------+ 
| 1 | John | Doe | 50000 | 
| 2 | Jane | Doe | 55000 | 
| 3 | Jim | Smith | 60000 | 
| 4 | Sarah | Johnson| 65000 | 
+----+--------+--------+--------+

It is important to note that the SELECT INTO statement creates a new table with the same structure as the original table, but the data is not updated if changes are made to the original table. To keep the new table up-to-date, you would need to run the SELECT INTO statement again.

Conclusion

In this article, we have explored how to select data from a MySQL database and save it into a file or table. The SELECT INTO OUTFILE statement allows you to save the results of a SELECT statement into a file on the server, while the SELECT INTO statement allows you to create a new table and insert the data into it. Both of these statements can be very useful in certain situations and provide a simple way to export and import data in MySQL.

Related Articles