"MERGE command in SQL Server 2008 and up makes this a no brainer."

Yup, MERGE in SQL 2008 can handle all the scenarios in one statement (albeit a complex one)

Here's the syntax:

merge [TargetData] t 
         using   [IncomingSourceData]   s     -- this could have been the result of a subquery or CTE
                        on t.ID = s.ID       
 	  	   when matched  and  (t.name <> s.name or t.Rate <> s.Rate   )  
   		                    then update  set t.Name = s.Name , t.Rate = s.Rate
 		  when not matched then insert (ID, Name, Rate, ActiveFlag)    
                                                  values(s.ID, s.Name, s.Rate, 1)   
		   when not matched by source and t.ActiveFlag = 1  
                                               then update set t.ActiveFlag = 0;  -- row wasn't in IncomingSource, so deactivate

MERGE is generally less costly and can run anywhere from 30% faster to over 2X faster than separate DML statements. It's become popular in data warehousing scenarios.

Prior to SQL 2008, you'd need separate DML statements (I'm sure people can and will come up with variations on these)

insert into TargetData  (ID, Name, Rate, ActiveFlag)
    select ID, Name, Rate, 1 from IncomingSourceData 
	       where not exists(  select * from TargetData where TargetData.ID  =  IncomingSourceData.ID)

update TargetData set Name = IncomingSourceData.Name, Rate = IncomingSourceData.Rate
         from TargetData
		     join IncomingSourceData on TargetData.ID = IncomingSourceData.ID
			    and (TargetData.Rate <> IncomingSourceData.Rate or TargetData.Name <> IncomingSourceData.Name)


update TargetData set ActiveFlag = 0 WHERE NOT EXISTS 
                    ( SELECT * FROM IncomingSourceData WHERE IncomingSourceData.ID = TargetData.ID)