SELECT INTO in MySQL

MySQL is an open-source Relational Database Management System that stores data in a structured format using rows and columns. MySQL is a platform-independent language and can be compiled on any platform. MySQL is generally used for storing, manipulating, and retrieving data in RDBMS by using the SQL (Structured Query Language).

In this article, we will learn about the SELECT INTO statement in MySQL which serves several purposes in MySQL such as copying data from a new table, copying particular columns from a table, creating a backup, and so on.

SELECT INTO Statement

The SELECT INTO statement in SQL retrieves data from a database table and stores it into variables or a new table. It's commonly used to copy data from one table to another or to assign values from a query result to variables. This statement helps streamline data manipulation tasks by simplifying the process of retrieving and storing data.

Syntax:

SELECT column1, column2

INTO new_table

FROM old_table;

The parameters used in the SELECT INTO statement are as follows:

• column1, column2: Columns from the old_table that you want to select data from.
  
• new_table: The name of the new table where you want to store the selected data.
  
• old_table: The name of the existing table from which you want to select data.

Example of SELECT INTO in MySQL

In the given example we created our first table "students" with columns id, name, department, and salary, and inserted some data into the table, then we created the "students_archive" table with the same structure using the "LIKE" keyword. After this, we selected data from the "students" table using the "SELECT INTO" statement under the condition where salary is greater than 55000 and inserted it into the "students_archive" table.

Example 1: Archiving Data from the students table into the students_archive Table

-- Creating students table
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- Inserting some sample data into students table
INSERT INTO students(id, name, department, salary)
VALUES
    (1, 'Manvi', 'Engineering', 60000),
    (2, 'Juhi', 'Marketing', 55000),
    (3, 'Navya', 'HR', 50000);

students_archive table:

-- Creating students_archive table with the same structure
CREATE TABLE students_archive LIKE students;

-- Archiving data from studentstable into students_archive table
INSERT INTO students_archive (id, name, department, salary)
SELECT id, name, department, salary
FROM students
WHERE salary > 55000;

Output:

| id | name  | department  | salary |
|----|-------|-------------|--------|
| 1  | Manvi | Engineering | 60000  |

Example 2: Selecting Data into Variables from the Class The table where the department is 'Engineering'.

The SELECT INTO statement can also be used to select the data from the table and insert it to store it into the variable.

In this, we will be creating a table and inserting some data into it by the 'VALUE' clause and then we will create two variables "var_id" and "var_name". Then we will use the "SELECT INTO" statement to select the data from the table and store it in the variables.

-- Create class table
CREATE TABLE class (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- Insert sample data into class table
INSERT INTO class (id, name, department, salary)
VALUES
    (1, 'Prakhar', 'Engineering', 60000),
    (2, 'Navya', 'Marketing', 55000),
    (3, 'Manvi', 'HR', 50000);

Declare variables to store data:

-- Declare variables to store data
DECLARE var_id INT;
DECLARE var_name VARCHAR(100);

-- Select data into variables
SELECT id, name
INTO var_id, var_name
FROM class
WHERE department = 'Engineering'
LIMIT 1;
-- Output the selected data
SELECT var_id, var_name;

Output: Values Stored in the variables

| var_id | var_name |
|--------|----------|
| 1      | Prakhar  |

Conclusion

In MySQL, the "SELECT INTO" statement is generally used to select the data from the table and insert it into another table but it can also be used in many other ways. In this article we have used the "SELECT INTO" statement to select the data and insert it into another table also we have stored the selected data into the variables.