Retrieve Father and Child structure in a Database Table

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 #MyTempTable

DECLARE @ID CHAR(11)

SET @ID = 13192

SELECT *
INTO #MyTempTable
FROM [MyTable]
WHERE [ID] = @ID

SELECT @ID = [FatherID]
FROM #MyTempTable
WHERE [ID] = @ID

WHILE @ID <> 0
BEGIN
    INSERT INTO #MyTempTable
    SELECT *
    FROM [MyTable]
    WHERE [ID] = @ID

    SELECT @ID = [FatherID]
    FROM #MyTempTable
    WHERE [ID] = @ID
END

SELECT *
FROM #MyTempTable
ORDER BY [ID]

The perfomance are fine, but I want improve the readability. Any suggestions?

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 change

DECLARE @ID CHAR(11)
SET @ID = 13192

to

DECLARE @ID CHAR(11) = 13192

And 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 #MyTempTable

DECLARE @ID CHAR(11) = 13192

CREATE TABLE #MyTempTable(ID INT, FatherID INT)

WHILE @ID <> 0
BEGIN
    INSERT INTO #MyTempTable
    SELECT *
    FROM [MyTable]
    WHERE [ID] = @ID

    SELECT @ID = [FatherID]
    FROM #MyTempTable
    WHERE [ID] = @ID
END

SELECT *

FROM #MyTempTable
ORDER BY [ID]

DROP TABLE #MyTempTable

Here is an SQL Fiddle.

StackExchange Code Review Q#60580, answer score: 4