Is there really no way in SQL to update all fields in a table without explicitly typing field names As indicated in this post Update all columns from another table?

I need to update a table with 50 columns with values from another table, and then insert new records from a different table (with 50 columns).  I really don't want to list 50 name-value pairs (or use the bulk updating syntax here Bulk update of all columns, which SQL Server doesn't seem to like anyway).

So, I can conclude one of the following:


There IS a way after all,
It's ridiculous to have a table with 50 columns, or
Database Admins are masochists.
Unless I've missed something here - and I may have - I think this is fairly straightforward. 

Essentially the only problem here is you don't want to type out all the column names- that can easily be automated with a bit of dynamic SQL using the sys tables as hinted at by Randolph West. 

Something like 

select @Sql = COALESCE(@Sql + ', ', '') + COLUMN_NAME
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    TABLE_NAME='Table'


Which will list out all the columns separated with commas, as if you typed them ready for insert/update (or select).

This post: Selecting column name dynamically in an insert query is a stack overflow question that goes into the detail.

EDIT:

Just thought I'd add for clarity and to consolidate some comments:- 

Anything can be added to the @Sql 'building' bit, for example if you want the table name put before the column name, the select part would be:

select @Sql = COALESCE(@Sql + ', ', '') + TABLE_NAME + '.' + COLUMN_NAME


You can execute that variable using 

sp_executesql @Sql


Or just 

SELECT @Sql


to give it to you in a query execution window, which you can copy &amp; paste and run (which I would usually do - you can double check the code first and edit if needed)