Is there really no way in SQL to update all fields in a table without explicitly typing field names

Question

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:

1. There IS a way after all,
2. It's ridiculous to have a table with 50 columns, or
3. Database Admins are masochists.

Answer 1

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)

Answer 2

It's not pretty and a bit of surgery needs to be done with the resulting statement (remove trailing comma and add the table joins), but I have used this in the past...

DECLARE @UpdateStatement VARCHAR(max)

SET @UpdateStatement = 'UPDATE LeftTable SET ' + CHAR(10)

SELECT @UpdateStatement = @UpdateStatement + 'LeftTable.' + COLUMN_NAME + ' = RightTable.' + COLUMN_NAME + ', ' + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'YourSchema'
AND TABLE_NAME = 'YourTable'

PRINT @UpdateStatement

Answer 3

This stored procedure loops through fields and creates inserts followed by updates to bring two tables in line... I use it for copying data from one db to another (it doesn't do any deletes).

create procedure spInsertUpdateData (
 @tbl_name varchar(255),
 @srcDB varchar(255),
 @destDb varchar(255),
 @primaryKey varchar(255))
as 
 begin
declare @sql nvarchar(max)
set @sql = '';

-- inserts

declare @cols varchar(max)
select @cols = COALESCE(@cols + ', ', '') + COLUMN_NAME
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    TABLE_NAME=@tbl_name;

set @sql= 
'set identity_insert ' + @destDb  +'.dbo.' + @tbl_name  +' on;'  +
'insert into ' + @destDb  +'.dbo.' + @tbl_name + '(' + @cols +') select ' + @cols + ' from '  + @srcDB + '.dbo.' + @tbl_name + ' where not exists (select 1 from ' + @destDb + '.dbo.' + @tbl_name + ' x where x.' + @primaryKey  + '='   + @srcDb +'.dbo.' + @tbl_name + '.' + @primaryKey  + ');'+
'set identity_insert ' + @destDb  +'.dbo.' + @tbl_name  +' off;'  

select @sql;
exec sp_executesql @sql

--updates

set @cols =null
select @cols = COALESCE(@cols + ', ', '') + COLUMN_NAME + '=x.' + COLumn_name
from 
    INFORMATION_SCHEMA.COLUMNS
where 
    TABLE_NAME=@tbl_name and (not(COLUMN_name like @primaryKey));


set @sql = 'update dest set ' + @cols  + ' from ' + @srcDB +'.dbo.' + @tbl_name + ' x, ' +  @destDb + '.dbo.' + @tbl_name + ' dest where dest.' +@primaryKey + '=x.' + @primaryKey + ';'

select @sql;
exec sp_executesql @sql;

end
go

And is called like this:

exec spInsertUpdateData 'lkp_conditions','srdb','destdb','condition_id';

Answer 4

Using SQL Server Management Studio, you can very easily get a list of column names by drag-and-drop:

Simply drag-and-drop the "Columns" heading from the desired table onto the query window, and "hey presto", all the column names are magically typed into the query window.

See my answer on another question for more details about how to use search-and-replace with regular expressions to further edit the list of column names.