SELECT INTO

SELECT INTO

A SELECT INTO statement is used to create a new table containing or not containing the result set returned by a select query. SELECT INTO copies the exact table structure and data into another table specified in the INTO clause. Usually a select query returns result sets to the client application.

The columns of the newly created table inherit the column names, their data types, whether columns can contain null values or not, and any associated IDENTITY property from the source table. However the SELECT INTO clause does have some restrictions: it will not copy any constraints, indexes, or triggers from the source table. [Source: Beginning C# 2008 Databases: From Novice to Professional By Vidya Vrat Agarwal, James Huddleston, Ranga Raghuram]

In simple words "The SELECT INTO statement selects data from one table and inserts it into another table."

The SELECT INTO statement can be used to create backup copies of tables.

As an example suppose we want to make an exact copy of the data in our "Employees" table.

We will use the below SQL statement:

SELECT *
INTO Employees_Backup
FROM Employees

We can also use the IN clause to copy the table into another database:

SELECT *
INTO Employees_Backup IN 'My_Backup.mdb'
FROM Employees

You can also copy limited number of fields into the new table:

SELECT LastName,FirstName,Ecode
INTO Employees_Backup
FROM Employees

SQL SELECT INTO - along With a WHERE Clause

The below SQL statement creates a "Employees_Backup" table with only the Employees who live in the city "London":

SELECT LastName,Firstname,Ecode
INTO Employees_Backup
FROM Employees
WHERE City='London'

More on SELECT INTO:

How to SELECT * INTO [temp table] FROM [Stored Procedure]
Stackoverflow

SQL Server SELECT INTO and Blocking With Temp Tables
Stackoverflow