Retrieve Father and Child structure in a Database Table
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 &lt;&gt; 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.