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 & paste and run (which I would usually do - you can double check the code first and edit if needed)

StackExchange Database Administrators Q#147562, answer score: 5