T-SQL Recursive SQL Query

Question

How to write a recursive SQL query in T-SQL? Can you give a simple example of such a recursive SQL query.

Answer 1

CREATE TABLE ATable (ID INTEGER, ParentID INTEGER) INSERT INTO ATable SELECT 1, NULL UNION ALL SELECT 2, 1 UNION ALL SELECT 3, 2 ;WITH q AS ( SELECT ID, ParentID FROM ATable UNION ALL SELECT a.ID, a.ParentID FROM ATable a INNER JOIN q ON q.ID = a.ParentID ) SELECT DISTINCT * FROM q 

Answer 2

Here is an example.

 Declare @Temp table ( ID int, ParentID int, Happened date, Value int ) Insert into @Temp Values (1, null, dateadd(day,1,GetDate()),1), (2, 1, dateadd(day,2,GetDate()),2), (3, 1, dateadd(day,3,GetDate()),3), (4, null, dateadd(day,4,GetDate()),10), (5, 3, dateadd(day,5,GetDate()),50), (6, 4, dateadd(day,5,GetDate()),50), (7, 5, dateadd(day,5,GetDate()),90); ---------------------------------------- with Magic as ( select * from @Temp Where ID = 1 union all select t.* from Magic m inner join @Temp t on t.ParentID = m.ID ) select * from Magic option (maxrecursion 3) 

Answer 3

Read the following link on how to write recursive queries using common table expressions: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Answer 4

Watch and read:

Recursive queries using common table expressions