As said before, the AS clause in the SELECT statement is for aliasing the columns, which is disregarded by the INSERT statement.

Your statement should have looked like this:

INSERT INTO #ATable (col2, col1, col3)

...

if you wanted to change the order of inserts.

Appreciate the effort of putting this in a QoTD.

(and I was looking frantically for 5 minutes to find the "catch". :-D)