Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
SQL SELECT INTO
The SELECT INTO statement is used in SQL to create a new table and insert the results of a query into that table. It combines the creation of a new table with the insertion of data, allowing you to create a table based on the results of a query or data manipulation operation.
Purpose
- Create New Table: Allows you to create a new table with the structure and data resulting from a query.
- Data Transformation: Useful for creating backup tables, summary tables, or intermediate tables for complex data processing.
Syntax
SELECT column_name(s)
INTO new_table_name
FROM existing_table_name
WHERE condition;
Example:
1. Create a Backup Table of Employees
This query creates a new table named backup_employees containing the data from the employees table.
SELECT *
INTO backup_employees
FROM employees;
Output:
A new table backup_employees is created with the same structure and data as the employees table.
2. Create a Summary Table for High Salaries
This query creates a table named high_salary_employees with employees earning more than 50,000.
SELECT employee_id, first_name, salary
INTO high_salary_employees
FROM employees
WHERE salary > 50000;
Output:
A new table high_salary_employees is created with the structure:
employee_id | first_name | salary |
1 | Rajesh | 55000 |
2 | Priya | 48000 |
3. Create a Table for Department Performance
This query creates a table department_performance with average salaries for each department.
SELECT department_id, AVG(salary) AS avg_salary
INTO department_performance
FROM employees
GROUP BY department_id;
Output:
A new table department_performance is created with the structure:
department_id | avg_salary |
1 | 45000 |
2 | 40000 |
4. Create a Table for Orders from a Specific Date
This query creates a table named orders_from_2024 containing orders placed in the year 2024.
SELECT order_id, order_date, order_amount
INTO orders_from_2024
FROM orders
WHERE YEAR(order_date) = 2024;
Output:
A new table orders_from_2024 is created with the structure:
order_id | order_date | order_amount |
101 | 2024-01-15 | 1500 |
102 | 2024-03-10 | 2000 |
Tips to Remember
- New Table Creation: SELECT INTO creates a new table with the result set of the query. The new table’s structure is derived from the columns selected in the query.
- Data Type Inference: The data types of the columns in the new table are automatically inferred from the columns selected in the SELECT statement.
- No Existing Table: If the new table already exists, SELECT INTO will result in an error. Use INSERT INTO for existing tables or CREATE TABLE followed by INSERT INTO for more control.
- No Primary Key or Indexes: The new table created by SELECT INTO does not include primary keys, indexes, or constraints. These need to be added separately if required.
Power of SELECT INTO
- Data Backup and Recovery: Useful for creating backup copies of tables or for disaster recovery scenarios.
- Data Transformation: Facilitates the creation of new tables based on specific queries, useful for data analysis and reporting.
Frequently Asked Questions
Q1: Can I use SELECT INTO to copy a table structure without data?
A1: No, SELECT INTO copies both the structure and data. To copy only the structure, use CREATE TABLE with a SELECT statement without inserting any data.
Example:
CREATE TABLE new_table_name AS
SELECT *
FROM existing_table_name
WHERE 1 = 0; — No rows will be inserted
Q2: Can I use SELECT INTO to create a table in another database?
A2: Yes, you can specify a different database in the table name if you have permissions to create tables in that database.
Example:
SELECT *
INTO other_database.new_table
FROM existing_table;
Q3: How can I include indexes or constraints in the new table created by SELECT INTO?
A3: After creating the table with SELECT INTO, you need to use ALTER TABLE statements to add indexes, primary keys, or constraints.
Example:
ALTER TABLE new_table
ADD PRIMARY KEY (id);
Q4: What if the SELECT INTO query returns a large result set?
A4: Be cautious with large result sets, as they can affect performance and disk space. Ensure sufficient resources and consider indexing or partitioning strategies for large tables.
Q5: Can I use SELECT INTO with complex queries involving joins?
A5: Yes, you can use SELECT INTO with complex queries, including joins, aggregations, and subqueries.
Example:
SELECT e.employee_id, d.department_name, e.salary
INTO employee_department_summary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;
