SELECT INTO Statement
SELECT INTO statement allows you to create a temporary or permanent table and populate it with records from a SELECT statement.
SYNTAX:
SELECT Column1, Column2,…
INTO NewTableName
FROM ExistingTableName
WHERE [condition]
Query using the above syntax when executed will copy all the columns listed in the SELECT statement along with their definition and data into the new table. SELECT INTO statement can also be used to create a new table based on columns and data combined from multiple tables or views.
Table: Employee
EmployeeID FirstName LastName Title HireDate ManagerID EmployeeLocationID
1 George Cruz CEO 12/8/2008 NULL 1
2 John Mathew Vice President 4/1/2009 1 1
3 Ethan William Director I 8/8/2009 2 2
4 Jacob Logan Director II 12/8/2011 3 NULL
5 Nathan Mateo Senior Manager 9/3/2009 4 2
6 Alice Charles Manager I 12/8/2012 5 1
7 John Robert Manager II 10/8/2011 6 2
8 Sophia James Manager III 5/8/2011 7 NULL
9 Kevin Chan Product Manager 7/8/2011 5 1
10 Susan Mortiz Solutions Arthitect 1/3/2012 6 1
11 Martha Miller Systems Arthitect 12/15/2012 10 1
Table: Location
LocationID LocationCode LocationName
1 L001 San Francisco
2 L002 New York
3 L003 Chicago
4 L004 Los Angeles
Example
Query :
SELECT E.EmployeeID, E.FirstName + ' ' + E.LastName AS Name, L.LocationName AS Location
INTO EmployeeLocation
FROM Employee AS E
INNER JOIN Location AS L ON E.EmployeeLocationID = L.LocationID
GO
SELECT *
FROM EmployeeLocation
Output :
EmployeeID Name Location
1 George Cruz San Francisco
2 Steve Mathew San Francisco
3 Ethan William New York
5 Nathan Mateo New York
6 Alice Charles San Francisco
7 John Robert New York
9 Kevin Chan San Francisco
10 Susan Mortiz San Francisco
11 Martha Miller San Francisco
Description :
The above query creates a new table called EmployeeLocation and adds three columns to it with definitions of the columns copied from Employee and Location tables.