SQL SELECT INTO – Understanding the “SELECT INTO” Statement

SQL SELECT INTO a single command, you can accomplish two tasks of create a new table and fill it with data from an existing table.

SQL (Structured Query Language) is a standard language for managing data held in a relational database management system (RDBMS) or for stream processing in a relational data stream management system (RDSMS). One of the key statements in SQL is the “SELECT INTO” statement.

The “SELECT INTO” statement copies data from one table and inserts it into a new table.

The Syntax

The basic syntax of the “SELECT INTO” statement in SQL is as follows:

SELECT column1, column2, column3, ...
INTO new_table_name
FROM existing_table_name;

In this syntax:

• SELECT column1, column2, column3, ... specifies the columns that you want to copy from the existing table. You can select as many columns as you want or use a * to select all columns.
• INTO new_table_name specifies the name of the new table that will be created. This table will hold the copied data.
• FROM existing_table_name specifies the name of the existing table from where the data will be copied.

Example: Using “SELECT INTO”

Let’s use a concrete example to understand how to use the “SELECT INTO” statement. Assume You have the following Employees table:

| EmployeeID | FirstName | LastName | BirthDate  | Position   |
|------------|-----------|----------|------------|------------|
| 1          | John      | Doe      | 1990-01-01 | Manager    |
| 2          | Jane      | Smith    | 1992-02-02 | Analyst    |
| 3          | Mike      | Johnson  | 1988-03-03 | Developer  |

Now, let’s say You want to create a new table, Managers, which will include all data from the Employees table where the Position is ‘Manager’. you can use the “SELECT INTO” statement to do this:

SELECT *
INTO Managers
FROM Employees
WHERE Position = 'Manager';

After executing this statement, the Managers table will be created and it will look like this:

| EmployeeID | FirstName | LastName | BirthDate  | Position   |
|------------|-----------|----------|------------|------------|
| 1          | John      | Doe      | 1990-01-01 | Manager    |

The Managers table contains all columns from the Employees table, but only includes rows where Position is ‘Manager’.

Points to Note

1. The “SELECT INTO” statement will copy all the columns from the existing table into the new table. If you want to copy only certain columns, you need to specify them after the “SELECT” keyword.

2. The new table will be created in the default filegroup and will have the same structure, settings, and constraints as the original table.

3. The “SELECT INTO” statement can also be used to create a backup copy of an existing table.

4. It’s important to note that the “SELECT INTO” statement is not supported in all SQL databases. For instance, in MySQL, you would use the “CREATE TABLE AS” or “INSERT INTO SELECT” statement instead.

5. Be mindful when using “SELECT INTO” to ensure that you don’t unintentionally overwrite existing data or create duplicates.

The “SELECT INTO” statement is a powerful SQL command that allows you to efficiently create new tables from existing data, making it an essential tool for any SQL user. Whether you are performing backups, creating subsets of existing data, or transferring data between databases, “SELECT INTO” can make your SQL tasks easier and more efficient.