I have a table with this schema:CREATE TABLE [td].[MyTable] (     [ID] [int] NOT NULL     ,[FatherID] [int] NULL)(Note: I have excluded all the columns not relevant to the discussion)I receive an [ID] as input, and I need to collect the relative record and all of its fathers in one single output.Every record has the id of the Father stored in the [FatherID] column. There is only one root element and it is recognized when we found the [FatherID] = 0.This is the current full working query:IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL DROP TABLE #MyTempTableDECLARE @ID CHAR(11)SET @ID = 13192SELECT *INTO #MyTempTableFROM [MyTable]WHERE [ID] = @IDSELECT @ID = [FatherID]FROM #MyTempTableWHERE [ID] = @IDWHILE @ID <> 0BEGIN    INSERT INTO #MyTempTable    SELECT *    FROM [MyTable]    WHERE [ID] = @ID    SELECT @ID = [FatherID]    FROM #MyTempTable    WHERE [ID] = @IDENDSELECT *FROM #MyTempTableORDER BY [ID]The perfomance are fine, but I want improve the readability. Any suggestions?
Retrieve Father and Child structure in a Database Table
sql;tree;sql server
Firstly, you use both the SELECT [XYZ] INTO [ABC]and the INSERT INTO [ABC] SELECT [XYZ] syntax in the same query, using one would make the query much more consistent and easier to read. Personally, I prefer using INSERT INTO [ABC] SELECT [XYZ], you do have to explicitly create the temporary table though, but that makes it usually clearer what is happening.If you'd prefer, you can changeDECLARE @ID CHAR(11)SET @ID = 13192toDECLARE @ID CHAR(11) = 13192And you should really explicitly drop the #MyTempTable at the end of the query.Lastly, the biggest change I would suggest is to completely remove the selects from outside of the while, they are completely redundant. If you remove those selects from outside the while loop, then the first iteration of the while does exactly the same as the selects on the outside would have done.Here is what we end up with:IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL DROP TABLE #MyTempTableDECLARE @ID CHAR(11) = 13192CREATE TABLE #MyTempTable(ID INT, FatherID INT)WHILE @ID <> 0BEGIN    INSERT INTO #MyTempTable    SELECT *    FROM [MyTable]    WHERE [ID] = @ID    SELECT @ID = [FatherID]    FROM #MyTempTable    WHERE [ID] = @IDENDSELECT *FROM #MyTempTableORDER BY [ID]DROP TABLE #MyTempTableHere is an SQL Fiddle.