@kapil_kk,

I don't see any obvious severe problems in the code. I agree with Gail, though. One test is worth a thousand expert opinions. Run the code and if performance and resource usage is satisfactory for as much as as it is going to be used, then leave it alone.

On the outside chance that you need some improvement, you would probably get some additional performance if you converted the LEFT OUTER JOIN/WHERE IS NULL things to WHERE NOT EXISTS. As already mentioned, consolidation of some of the updates

Rumor also has it (I've not personally tested it) that traditional "upserts" are faster than MERGE in SQL Server. I can't put my finger on the articles that did the performance comparisons but I'm sure that brother Google could help.

Instead of just getting rid of the WITH(NOLOCK) on the temp table, change it to a WITH(TABLOCKX) for a possible small improvement. This will prevent smaller locks form going through incremental lock escalation. No one else can get to the table, anyway.

It's also great to see nicely formatted code such as that as you've written not to mention meeting some of my favorite best practices.