The SELECT INTO statement in SQL Server is a powerful tool for creating new tables and copying data from existing tables or queries. This guide will walk you through the process of using the SELECT INTO statement in SQL Server, providing comprehensive steps and code snippets you can use directly in SQL Server Management Studio (SSMS).
Understanding the SQL SELECT INTO Statement
The SELECT INTO statement creates a new table and inserts the result set of a query into it. This is useful for creating backups, archiving data, or creating new tables based on existing data. The basic syntax for the SELECT INTO statement is:
SELECT column1, column2, ...
INTO new_table
FROM existing_table
WHERE condition;Steps to Perform a SELECT INTO in SQL Server
- Identify the Source and Destination: Determine which table or query you want to copy data from and specify the new table where the data will be stored.
- Write the SELECT INTO Statement: Formulate the SELECT INTO statement with the appropriate columns and conditions.
- Execute the Statement: Run the statement in SSMS to create the new table and insert the data.
Example 1: Copying All Data from an Existing Table
Let’s start with a simple example where we copy all data from the Employees table to a new table named EmployeesArchive.
SELECT *
INTO EmployeesArchive
FROM Employees;Example 2: Copying Specific Columns
You can also copy specific columns from the source table. Suppose we only want to copy the FirstName, LastName, and Department columns.
SELECT FirstName, LastName, Department
INTO EmployeesArchive
FROM Employees;Example 3: Copying Data with a Condition
You can use a WHERE clause to copy only the data that meets certain conditions. For example, let’s copy all employees from the Sales department to a new table.
SELECT *
INTO SalesEmployees
FROM Employees
WHERE Department = 'Sales';Example 4: Creating a New Table from a Complex Query
You can create a new table from a complex query involving joins and calculations. Suppose we want to create a table with the total salary by department.
SELECT Department, SUM(Salary) AS TotalSalary
INTO DepartmentSalaries
FROM Employees
GROUP BY Department;Example 5: Using SELECT INTO with Temporary Tables
The SELECT INTO statement can also be used to create temporary tables, which are useful for intermediate processing in complex queries.
SELECT *
INTO #TempEmployees
FROM Employees
WHERE Department = 'IT';
-- You can now use #TempEmployees in subsequent queries
SELECT * FROM #TempEmployees;Best Practices for Using SELECT INTO Statements
1. Specify Columns Explicitly: Whenever possible, specify the columns explicitly to avoid unintended changes if the source table schema changes.
2. Check for Existing Tables: Ensure that the new table does not already exist, as SELECT INTO will fail if the table already exists.
3. Use Appropriate Data Types: Be mindful of the data types in the source table, as the new table will inherit these data types.
4. Indexing and Constraints: Remember that SELECT INTO does not copy indexes, primary keys, or constraints. You will need to create these separately if needed.
You can create new SQL Server tables using Excel
The SELECT INTO statement in SQL Server is a powerful tool for creating new tables and copying data if you have the right technical skills. There is another easy way to create SQL Server tables especially if you or people in your team don’t have any technical skills. You can create SQL Server tables (even with data) using the New SQL Server Table from Excel feature in the SQL Spreads Excel Add-In. You can quickly connect to your SQL Server database from within Excel, and create your new table with data in SQL Server. Download the SQL Spreads Excel Add-In to try it out for free.
For more tips and detailed guides on SQL Server, visit our SQL Spreads blog.
