MySQL

The SELECT INTO statement in MySQL is used to select data from one or more tables and copy it into another table or variables. It’s useful when you want to back up data, create temporary tables, or extract data into specific variables. For beginners, understanding this command is crucial for effectively managing data in MySQL databases.

Example Scenario:

You are working on a database related to a company with tables for employees, departments, and branches, and you need to copy data from one table to another or extract values into variables.

Steps to Use the SELECT INTO Statement:

1. Basic Syntax of SELECT INTO for Tables

   The general format for copying data into a new table is:

   SELECT column1, column2, column3, ...
   INTO new_table
   FROM existing_table
   WHERE condition;
   

2. Example 1: Copying employees into a New Table

   Suppose we want to copy all employees from the employees table into a new table called backup_employees:

   CREATE TABLE backup_employees LIKE employees;
   INSERT INTO backup_employees
   SELECT * FROM employees;
   

   Explanation:

   • The first query creates a new table backup_employees with the same structure as the employees table.
   • The second query copies all the data from employees to backup_employees.
3. Example 2: Copying Specific Columns into a New Table

   Suppose you only want to copy employee names and their hire dates:

   CREATE TABLE employee_names_dates (
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   hire_date DATE
   );
   INSERT INTO employee_names_dates
   SELECT first_name, last_name, hire_date
   FROM employees;
   

   • Explanation:
     • This query selects only the first_name, last_name, and hire_date columns from employees and inserts the data into a new table employee_names_dates.
4. Using SELECT INTO to Copy Data with Conditions

   You can use the WHERE clause to copy only specific rows. For instance, copying employees from department 1:

   INSERT INTO backup_employees
   SELECT * FROM employees
   WHERE department_id = 1;
   

   • Explanation:
     • This query copies only the employees who belong to department 1 into the backup_employees table.
5. SELECT INTO for Variables (in Stored Procedures)
   • In stored procedures, you can use SELECT INTO to store values from a table into variables:

     DECLARE employee_name VARCHAR(100);
     DECLARE employee_hire_date DATE;
     SELECT CONCAT(first_name, ' ', last_name), hire_date
     INTO employee_name, employee_hire_date
     FROM employees
     WHERE employee_id = 1;
     

   • Explanation:
     • This query selects the full name and hire date of the employee with employee_id 1 and stores them into variables employee_name and employee_hire_date.
6. Copying All Data:
   • If you want to copy all data from one table to another, ensure both tables have matching column structures. Use SELECT *for simplicity, as in:

     INSERT INTO backup_employees
     SELECT * FROM employees;
     

7. Copying Data with Joins:
   • You can also use JOINs in the SELECT INTO statement to combine data from multiple tables and insert it into a new table. For example:

     INSERT INTO department_employees
     SELECT e.first_name, e.last_name, d.department_name
     FROM employees e
     JOIN departments d ON e.department_id = d.department_id;
     

   • Explanation:
     • This query copies the first name, last name, and department name for each employee into a new table department_employees.

Notes on Using the SELECT INTO Statement:

• Creating Target Tables: Before copying data into a new table, you must ensure that the table is created and has the right structure. The CREATE TABLE statement can be used for this purpose.

By following these steps, beginners can effectively use the SELECT INTO statement in MySQL to copy data into new tables or variables, particularly in scenarios involving company, employees, departments, and branches.

To gain complete access, login with gmail or outlook, no need of signup, click here