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