Ok having brain cramp. Have to two tables full table is 45Million rows. Will get records in 2nd table that were changed from another source. So instead of running a full update on records which match with cmsid, I want to replace the records in the full table with the records in the change table. Don't want cursor but did not see a way in merge to accomplish this
update t set t.col1 = u.col1, ... from target t join upd u on t.key = u.key
that I have hoping to find a way with merge but not seeing a way
UPDATE deletes and then inserts which is to replace.
My "MERGE", if you mean that you want to insert new records from the change table and update any existing records with values for columns from the change table (and even delete records that are not in the change table, if you so desire), then purpose of the MERGE statement is exactly that. One has to be careful when using MERGE - you can wipe out records from the target table if you are not careful. I have also read about performance problems in some cases. Nonetheless, it is there, and SQL Server has been supporting it since SQL 2008 or so if I am not mistaken.
BTW, Robert Sheldon's article on simple-talk is a good tutorial on MERGE
Merge into target t using upd u when matched by source set t.col1 = u.col1 ;
with sourceData as ( select ... from secondTable where ... ) merge into target t using sourceData as s on s.cmsid = t.cmsid when not matched by target then insert (...) values ( ... ) when matched and (s.col1 <> t.col1 or s.col2 <> s.col2 or s.col3 <> s.col3 ... then update set t.col1 = s.col1 ...