Insert if not exists then update - n number of columns update statement simplification

This question is in continuation of existing question here.

I need to compare two similar tables in two databases on the same instance and update the data if record exists and insert if not. I planning to use merge as given as answer to above question.

But I want to know whether there is any simple solution to update the entire table with other table without writing all the columns in the SET statement in UPDATE query.开发者_如何学C

I mean I dont want to write SET t1.col1 = t2.col2, t1.col2=t2.col2 and so on.

There is no simple way to do this besides creating the query dynamically and executing it dynamically, both of which are pure evilness.

Replace YourTableName name with whatever name your table has, copy the result to your merge statement and remove the last comma.

declare @S varchar(max)
set @S = 'SET '

select @S = @S + 'T1.'+name+'=T2.'+name+', '
from sys.columns
where object_name(object_id) = 'YourTableName'
order by column_id

select @S