How to Create a Table from a SELECT Statement in Microsoft SQL

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


About onlinejt

Blogger

No comments yet.

Leave a Reply

*