SQL Server: SELECT INTO Statement
This SQL Server tutorial explains how to use the SELECT INTO statement in SQL Server (Transact-SQL) with syntax and examples.
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).
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
- The columns or calculations that you wish to retrieve.
- The new table to create with the selected expressions and their associated definitions (new_table must not exist).
- 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.
- 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.
Let’s look at an example that shows how to use the SELECT INTO statement in SQL Server (Transact-SQL).
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.
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