SQL SELECT INTO Statement

Welcome to The Coding College! In this tutorial, we’ll explore the SQL SELECT INTO Statement, a powerful feature used to create a new table and copy data into it from an existing table. This guide will cover its syntax, practical examples, and tips for effective usage.

What Is the SQL SELECT INTO Statement?

The SQL SELECT INTO statement is used to copy data from one table into a new table. It simultaneously creates the new table and populates it with data based on the SELECT query.

Syntax

SELECT column_name(s)  
INTO new_table  
FROM existing_table  
WHERE condition;  

Key Points

1. Creates a New Table: Automatically creates the new_table with the same structure as the selected columns.
2. Copies Data: Transfers data from the existing_table based on the SELECT query.
3. No Constraints or Indexes: The new table will not inherit constraints (like primary keys) or indexes from the original table.

Example Tables

Table: employees

Example 1: Copy All Data

Query: Create a Backup Table

SELECT *  
INTO employees_backup  
FROM employees;  

Result

A new table employees_backup is created, containing all rows and columns from employees.

Example 2: Copy Specific Columns

Query: Create a Table with Selected Columns

SELECT employee_id, name  
INTO employees_summary  
FROM employees;  

Result

A new table employees_summary is created with only the employee_id and name columns.

Example 3: Copy Data with Conditions

Query: Copy Employees in the HR Department

SELECT *  
INTO hr_employees  
FROM employees  
WHERE department = 'HR';  

Result

Example 4: Copy Data into Another Database

Query: Copy Employees Data to Another Database

SELECT *  
INTO other_database.dbo.employees_backup  
FROM employees;  

Explanation

• Copies data from the employees table to the employees_backup table in the other_database.
• Replace dbo with the appropriate schema if needed.

Example 5: Combining Data from Multiple Tables

Query: Create a New Table with Data from Joins

SELECT e.employee_id, e.name, d.avg_salary  
INTO employees_with_avg_salary  
FROM employees e  
JOIN departments d  
ON e.department = d.department;  

Limitations of SELECT INTO

1. No Constraints or Indexes: The new table does not inherit constraints, indexes, or triggers.
2. Column Defaults: Default values from the source table are not applied in the new table.
3. SQL Server-Specific: While most databases support SELECT INTO, the exact syntax or behavior may vary.

Best Practices

1. Use Explicit Column Names: Avoid using SELECT * for better clarity and control.
2. Test Before Execution: Ensure the WHERE condition accurately defines the data to be copied.
3. Add Constraints Manually: Apply necessary constraints and indexes to the new table after creation.
4. Use for Small Datasets: For large datasets, consider using INSERT INTO ... SELECT for more control.

SELECT INTO vs INSERT INTO

Advanced Tips

• Temporary Tables: Use SELECT INTO to create temporary tables for intermediate results.

SELECT *  
INTO #temp_table  
FROM employees  
WHERE salary > 70000;  

• Backup Strategies: Employ SELECT INTO for quick table backups during development.

Conclusion

The SQL SELECT INTO statement is a versatile tool for creating and populating new tables. Its ability to combine data selection and table creation in a single step makes it an excellent choice for backups, temporary tables, and data transformations.

For more SQL tutorials and practical guides, visit The Coding College, your go-to platform for mastering coding and programming!