Joining a table onto itself in SQL and saving the result

Joining a table onto itself in SQL and saving the result

In SQL, I am joining a table onto itself:
SELECT * FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
So it's fetching all the rows which have the same NAME appearing in a row
of the other table (will also return the same row twice!).
Let's say that I want to save the result into a temporary table, say
something like:
SELECT * INTO ##temp_table FROM (
SELECT * FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME
)
Oh dear. SQL says:
The column 'NAME' was specified multiple times.
Reason: SQL can only create a table if every column name is unique.
Obvious solution: give every column name in the "second table" an alias.
My problem is that the actual tables I'm working with have about 40
columns. Giving every one of those columns an alias seems like a wasteful
use of time. Sure, I don't need every column so could drop some of them,
but deciding which ones I require just now also seems wasteful.
Question: is there a shorthand way to rename every column? For example,
can I append every column name with a _2 or an _a?