SQL Server Select into statement with example

The SELECT INTO statement creates a new table and inserts rows from the query into it.

If you want to copy the partial data from the source table, you use the WHERE clause to specify which rows to copy. Similarly, you can specify which columns from the the source table to copy to the destination table by specifying them in the select list.

Note that SELECT INTO statement does not copy constraints such as primary key and indexes from the source table to the destination table.

Syntax:

SELECT Column1, Column2, Column3
INTO MyNewTable
FROM MySourceTable;

Let's see with below examples:

• Create a backup copy of Customers:

SELECT * INTO Customers2017
FROM Customers;

• Copy records from one database to another.

SELECT * INTO Rohit.dbo.PurchaseOrderDetail  
FROM AdventureWorks2017.purchasing.PurchaseOrderDetail

• Copy only a few columns into a new table:

SELECT CustomerName, ContactName INTO Customers2017
FROM Customers;

• Copies only the US customers into a new table:

SELECT * INTO CustomersUS
FROM Customers
WHERE Country = 'US';

SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data.

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;

Create Employee and Manager Hierarchy using Common Table Expression

We will see how to get the Employee-Manager hierarchy from the same table.

Let's setup the table first.

CREATE TABLE dbo.Employee
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
ManagerID INT NULL
)

insert values into the table

INSERT INTO Employee VALUES (101, 'Ken', 'Sánchez', NULL)
INSERT INTO Employee VALUES (102, 'Keith', 'Hall', 101)
INSERT INTO Employee VALUES (103, 'Fred', 'Bloggs', 101)
INSERT INTO Employee VALUES (104, 'Joseph', 'Walker', 102)
INSERT INTO Employee VALUES (105, 'Zydr', 'Klyb', 101)
INSERT INTO Employee VALUES (106, 'Sam', 'Jackson', 105)
INSERT INTO Employee VALUES (107, 'Peter', 'Miller', 103)
INSERT INTO Employee VALUES (108, 'Chloe', 'Samuels', 105)
INSERT INTO Employee VALUES (109, 'George', 'Weasley', 105)
INSERT INTO Employee VALUES (110, 'Michael', 'Kensington', 106)

See the values below.

EmployeeID	FirstName	LastName	ManagerID
101	Ken	Sánchez	NULL
102	Keith	Hall	101
103	Fred	Bloggs	101
104	Joseph	Walker	102
105	Zydr	Klyb	101
106	Sam	Jackson	105
107	Peter	Miller	103
108	Chloe	Samuels	105
109	George	Weasley	105
110	Michael	Kensington	106

Below SQL Code to demonstrate the Employee and Manager relationship.

;WITH cteReports (EmpID, FirstName, LastName, SupervisorID, EmpLevel) AS
(
	SELECT EmployeeID, FirstName, LastName, ManagerID, 1
	FROM Employee
	WHERE ManagerID IS NULL
	UNION ALL
	SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
	FROM Employee AS e
	INNER JOIN cteReports AS r ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employee WHERE EmployeeID = cteReports.SupervisorID)
AS ManagerName
FROM cteReports
ORDER BY EmpLevel, SupervisorID

Output:

FullName	EmpLevel	ManagerName
Ken Sánchez	1	NULL
Keith Hall	2	Ken Sánchez
Fred Bloggs	2	Ken Sánchez
Zydr Klyb	2	Ken Sánchez
Joseph Walker	3	Keith Hall
Peter Miller	3	Fred Bloggs
Sam Jackson	3	Zydr Klyb
Chloe Samuels	3	Zydr Klyb
George Weasley	3	Zydr Klyb
Michael Kensington	4	Sam Jackson