CTE (Common Table Expression)SQL
The Common Table Expressions (CTE) are imported into the SQL to simplify many classes of the Structured Query Language (SQL) for a derived table, which is unsuitable. It was introduced in 2005 SQL SERVER version.
The common table expressions (CTE) are a result set, which we reference with the SELECT, INSERT, UPDATE, or DELETE statement. In SQL 2008, we add a CTE for the unique MERGE statement.
How to use CTEs in T-SQL?
The use of Common Text Expression is to add the clause "WITH" before the SELECT, INSERT, UPDATE, DELETE or MERGE statement. The WITH clause contain one or more CTEs, which are separated by commas.
::::=
cte_name [(column_name[, ...])]__Write the name of column here
AS (cte_query)
We generate CTEs when we refer to any table. The CTE result set is not accessible to any statements when we run the particular statement.
Creating a Recursive table expression
The recursive CTE is used when we are working with hierarchical data. An example of hierarchical data in the table is the list of students in the group. For each student, the counter generates a ReferenceID and a NAME. The ReferenceID references itself like an employee ID in a recursive table. We use the CTE to display the position of employee's database.
If the CTE is created wrong, it enters into the infinite loop.
To prevent the endless loop, MAXRECURSION will added in the OPTION clause of the INSERT, DELETE, UPDATE, SELECT or MERGE statement.
Use below code to create a table:
{
EmployeeID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastNamevarchar(50) NOT NULL,
Manager ID int NULL
}
INSERT INTO Employees VALUES(1, 'Ken', 'Thompson', NULL)
INSERT INTO Employees VALUES(2, 'Kent', 'Thompson', 1)
INSERT INTO Employees VALUES(3, 'Williams', 'Thompson', 1)
INSERT INTO Employees VALUES(4, 'Charles', 'Thompson', 2)
INSERT INTO Employees VALUES(5, 'Michal', 'Thompson', 3)
INSERT INTO Employees VALUES(6, 'Gill', 'Thompson', 3)
INSERT INTO Employees VALUES(7, 'Danyl', 'Thompson', 3)
INSERT INTO Employees VALUES(8, 'Monty', 'Thompson', 5)
INSERT INTO Employees VALUES(9, 'Rob', 'Thompson', 6)
INSERT INTO Employees VALUES(10, 'Robert', 'Thompson',6)
After the Employee table is created, a SELECT statement, which is preceded by a WITH clause that includes a CTE named cteReports is created:
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel
AS
(
Select EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
//WHERE ManagerID IS NULL
UNION ALL
SELECT e.StudentID, e.FirstName, e.LastName, e.ManagerID,
r. StuLevel + 1
FROM Students p
INNER JOIN cteReports s
ON e.ManagerID = r.StuID
)
SELECT
First Name+ ' ' + LAST NAME AS FullName,StuLevel,
(SELECT FirstName + '.....' +LastName FROM Students
WHERE StudentID = cteReports.MgrID) AS ManagerFROM cteReports
ORDER BY StuLevel, MgrID
It is an essential tool to generate the inconsistent result set, and retrieved in the SELECT, UPDATE, INSERT, MERGE, or DELETE the statement.
