Here are some examples in how to create a table a table from a select statement. This sample SQL script is for Microsoft SQL and to start I will be using a table called “Employees”.
Example of simple select statement:
SELECT EmployeeID, FirstName, LastName
FROM dbo.Employees
Results:
EmployeeID FirstName LastName
----------- ---------- --------------------
1 Nancy Davolio
2 Andrew Fuller
3 Janet Leverling
4 Margaret Peacock
5 Steven Buchanan
Now, this is how a table can be created from a select statement:
SELECT EmployeeID, FirstName, LastName
INTO exployees2
FROM dbo.Employees
Results:
SELECT * FROM exployees2
Output:
EmployeeID FirstName LastName
----------- ---------- --------------------
1 Nancy Davolio
2 Andrew Fuller
3 Janet Leverling
4 Margaret Peacock
5 Steven Buchanan
Here are some other complex queries:
SELECT EmployeeID, FirstName, LastName
INTO Employees_aList
FROM dbo.Employees WHERE LEFT(FirstName,1) = 'A'
Results:
SELECT * FROM Employees_aList
Output:
EmployeeID FirstName LastName
----------- ---------- --------------------
2 Andrew Fuller
9 Anne Dodsworth
Other samples from other tables:
USE Northwind;
SELECT *
INTO dbo.SalesOrderHeader
FROM Sales.SalesOrderHeader
WHERE Year(OrderDate)=2004
-- Create empty table
SELECT TOP (0)
SalesOrderID,
OrderDate
INTO SOH
FROM Sales.SalesOrderHeader
-- Create a table with condition
SELECT *
INTO SOH1
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate)=2004
-- Create a table with sorting values
SELECT *
INTO SOH2
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID desc
-- Create a table with functions
SELECT
[Year]=YEAR(OrderDate),
Orders=COUNT(*)
INTO SOH4
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
-- SQL Server select into temporary table - sql server select into temp table
SELECT TotalOrders = COUNT(*)
INTO #TotalOrders
FROM Sales.SalesOrderHeader
-- Create temporary table with functions
SELECT
ProductID = CAST(ProductID as INT),
ListPrice,
Color
INTO #ProdList
FROM Production.Product
ORDER BY ListPrice desc
-- Create a table with joining conditions
SELECT
soh.SalesOrderID,
OrderDate,
OrderQty,
ProductID
INTO SalesOrder
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID=sod.SalesOrderID