What is SQL SELECT INTO Statement?

The SQL SELECT INTO statement in SQL Server creates a new table from the existing table by copying the columns of the existing table. It is used for bulk data transfers.

The constraints such as not null, unique, etc. of the source table are not copied to the destination table. We can modify the SQL SELECT INTO statement to copy only a particular set of rows using the WHERE clause. Similarly, we can also select columns to be copied into the new table.

Syntax

The syntax for the SQL SELECT INTO statement in SQL Server is as follows:

The above query will copy N columns into the new_table from an existing table calledOld_table for all the rows where the condition in the where clause satisfies.

Parameters or Arguments

Let us look at all the parameters used and their meaning in the above SQL SELECT INTO Statement.

• column1, column2,...,columnN - It is the list of columns from the parent table (existing table) to be copied to the new table. We can also use * to copy all the columns of the old table.
• New_Table - It is the new table that is to be created by this query. Its name should be unique. It will contain all the columns specified by the query. This new table will not have constraints like not null, unique, etc. from the Old_table.
• Old_table - It is the parent table from which data is to be copied to the new table. We can also use the JOIN clause to join multiple existing tables.
• WHERE condition - It is an optional clause that can be used to select only particular records from the table. In case we only want rows that satisfy the given condition are only copied into the new table.

Data Types

In SQL Server, the data type of the column such as Varchar(), Integer, Date, etc remains the same in the new table as well.

When a primary key column (which is unique for each record, and can be used to uniquely identify the record) of the existing table is copied to the new table, the column inherits the primary key property as well. In the cases listed below, the primary key column doesn't inherit this property:

• The SELECT statement contains more than one table using join.
• UNION is used to join multiple SELECT statements.
• The identity column is either part of an expression or is listed more than once in the select list.

Even if any one of the above conditions is true the column created is not the primary key instead it is set to NOT NULL.

Limitations and Restrictions

The SQL SELECT INTO statement has a few limitations which are important to be noted as stated below:

• A partitioned table cannot be created using SQL SELECT INTO. It is not useful even when the parent or existing table is partitioned table.

Logging Behavior

Database logging is a method of keeping records of the changes made to the database or in a narrower vision, changes made to a particular table. They help in the recovery of the database from any kind of failure.

The amount of logging using the SQL SELECT INTO statement depends on the recovery model in the effect of the database. If the database is using a simple recovery model or bulk-logged recovery model, bulk operations are minimally logged. This makes the SELECT INTO Statement efficient. But the SELECT INTO statements are fully logged operations and thus make it inefficient. To make it minimally logged we can set the derived UserDataAccess property to 0.

Examples

Let us take a few examples to understand the working of the SQL SELECT INTO statement.

Example 1:

Let us create a Table Employee that stores employee details as EmpCode, EmpFullName, Job, Manager, HireDate, and DEPTCODE. Now, let us assume we wish to create another table that has EmpCode, EmpfullName, and DEPTCODE of all the software engineers only. To get this table from the Parent table Employee we will be using the SQL SELECT INTO statement.

Let us first create the EMPLOYEE table.

Output:

Output:

The above code copies specified rows and columns from the employee table to the Software_Engineers table.

Example 2

Let us take another example where we use join as well along with select into a statement. The first table will be CUSTOMERS and the other one will be ORDERS. We will combine these tables to get the order details and the address for delivery from the customer's table.

Creating Customers and Orders table

Output:

Output:

Output:

In the above example, we are performing a left join on two tables Order and Customer to generate a new table CurrentOrders using the SELECT INTO statement.

INSERT INTO SELECT vs SELECT INTO

• INSERT INTO AND SELECT INTO both statements can be used to insert data from one table to another. But INSERT INTO can only be used if the new table exists in our database. SELECT INTO statement can be used even if the target table does not exist. It automatically creates a new table if it doesn't exist.

  Let us say I have only one table called patients and I want to create another table that has a list of dental patients. Here I can use the SELECT INTO statement but if I use the INSERT INTO statement it will cause an error.

  Now, once I have both the tables and I wish to update existing table patients with entries from dental patients I can use the INSERT INTO statement.

• Another difference between the two statements is inserted inserts data in the predefined table but SELECT INTO defines a table by itself which sometimes may not structure the table as expected.

To learn more about SQL visit SQL OPERATORS next.