Selecting Into New Tables with SQL SELECT INTO
The SELECT INTO statement in SQL allows you to create a new table from the result set of a query. It is a powerful and flexible feature that is often used to copy data from one table to another, generate backup tables, or even create temporary tables for reporting or analysis. In this chapter, we will explore everything about the SELECT INTO statement, from basic to advanced, with examples and detailed explanations.
The SELECT INTO statement is used to select data from one table and insert it into a new table. Unlike the INSERT INTO ... SELECT statement, which copies data into an existing table, SELECT INTO creates a new table and inserts the data in one operation. This can be especially useful when you need to create backup copies of data or store the results of complex queries for later use.
Basic Syntax of SELECT INTO
The basic syntax of SELECT INTO is as follows:
SELECT column1, column2, ...
INTO new_table
FROM existing_table
WHERE condition;
column1, column2, ...: The columns you want to copy into the new table.new_table: The name of the new table that will be created.existing_table: The table from which data will be selected.condition: (Optional) The condition to filter data.
Copying Data into a New Table
The most straightforward use of SELECT INTO is to copy all data from an existing table into a new one.
Example:
Suppose we have a table called employees and we want to create a backup table.
SELECT *
INTO employees_backup
FROM employees;
Explanation: This query creates a new table called employees_backup and copies all data from the employees table into it.
Output:
A new table employees_backup is created, and all rows from the employees table are copied into it.
Copying Specific Columns into a New Table
If you only want to copy certain columns from an existing table, you can specify the columns in the SELECT statement.
Example:
Copy only the employee_id and name columns from the employees table into a new table called employees_summary.
SELECT employee_id, name
INTO employees_summary
FROM employees;
Explanation: This query creates a new table employees_summary and only copies the employee_id and name columns.
Output:
A new table employees_summary is created with only the employee_id and name columns.
Filtering Data with WHERE in SELECT INTO
You can filter the data that gets inserted into the new table using the WHERE clause.
Example:
Create a new table called high_earners that only includes employees with a salary greater than $50,000.
SELECT employee_id, name, salary
INTO high_earners
FROM employees
WHERE salary > 50000;
Explanation: This query creates a new table high_earners and inserts only those rows where the salary is greater than $50,000.
Output:
A new table high_earners is created, and only the employees who earn more than $50,000 are included.
Using SELECT INTO with Joins
You can also use SELECT INTO in conjunction with JOIN statements to create new tables from multiple related tables.
Example:
Suppose you have two tables, employees and departments, and you want to create a new table that stores both employee and department information.
SELECT e.employee_id, e.name, d.department_name
INTO employee_department
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
Explanation: This query creates a new table employee_department and combines data from both the employees and departments tables using an inner join.
Output:
A new table employee_department is created, containing employee and department information.
Creating Temporary Tables with SELECT INTO
Sometimes, you might want to create a table that exists only for the duration of a session. Temporary tables are useful in such cases. In most SQL implementations, temporary tables are prefixed with #.
Example:
Create a temporary table that stores a summary of employee salaries.
SELECT employee_id, salary
INTO #temp_salary_summary
FROM employees;
Explanation: This query creates a temporary table #temp_salary_summary that stores the employee_id and salary data.
Output:
A temporary table #temp_salary_summary is created, which will be deleted after the session ends.
Examples of Complex Queries with SELECT INTO
You can use SELECT INTO with complex queries, including aggregate functions, subqueries, and advanced filtering.
Example: Creating a Sales Summary Table
Suppose you have a sales table, and you want to create a new table that summarizes total sales by department.
SELECT department_id, SUM(sales_amount) AS total_sales
INTO sales_summary
FROM sales
GROUP BY department_id;
Explanation: This query creates a new table sales_summary that contains the total sales for each department.
Output:
A new table sales_summary is created with department IDs and their corresponding total sales amounts.
The SELECT INTO statement is a versatile and efficient tool in SQL, allowing you to create new tables based on the results of queries. Whether you're copying an entire table, selecting specific columns, or creating temporary tables, SELECT INTO is a valuable tool for managing and organizing your data. With the capability to combine it with filters, joins, and complex queries, SELECT INTO offers a robust solution for many database operations. Happy coding !❤️