Automatically match columns in INSERT INTO ... SELECT ... FROM

Always use explicit columns both in the INSERT and in the SELECT projection. Even if you don't want to, you should:

INSERT INTO T1 (C1, c2)
SELECT C1, C2 FROM T2

If T1 and T2 match exactly you have two choices. You can either select all columns from T2 for the insert into T1, or you can provide a column list to the insert statement.

Even though when you do a select MSSQL provides column headers that information is not used by an insert statement to match columns up.

Yes, you can omit the field names for the table that you insert to, and you can use select * to get all fields from the table, but I would not recommend this approach.

If you omit the field name the fields are matched by position, not by name. If the fields are not in the exact same order, they will be mixed up. Generally you should avoid relying on the exact layout of the tables, to minimise the risk that changes in the tables breaks the queries.