SQL Server: SELECT INTO Statement

In this post explains how to use the SELECT INTO statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT INTO statement is used to create a table from an existing table by copying the existing table's columns.

It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).

Syntax

The syntax for the SELECT INTO statement in SQL Server (Transact-SQL) is:

SELECT expressions
INTO new_table
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

new_table

The new table to create with the selected expressions and their associated definitions (new_table must not exist).

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

Note

• When using the SELECT INTO statement in SQL Server, the new_table must not already exist. If it does already exist, the SELECT INTO statement will raise an error.

Example

Let's look at an example that shows how to use the SELECT INTO statement in SQL Server (Transact-SQL).

For example:

SELECT employee_id, last_name, first_name
INTO contacts
FROM employees
WHERE employee_id  1000;

This SQL Server SELECT INTO example would select the employee_id, last_name, and first_name fields from the employees table and copy these fields along with their definitions to the new contacts table that does not yet exist.

Again, if there were records in the employees table, then the new contacts table would be populated with the records returned by the SELECT statement.

If you find that you want to rename some of the columns within the new table rather than using the original names, you can alias the column names in the SELECT INTO statement.

For example:

SELECT employee_id AS contact_id, last_name, first_name
INTO contacts
FROM employees
WHERE employee_id  1000;

In this SELECT INTO example, we don't want the first column in the new contacts table to be called employee_id. It would be more meaningful to rename the first column in the contacts table to contact_id. This is done by aliasing the employee_id column as follows:

employee_id AS contact_id