MERGE is notorious for some performance issues. You might want to try "UPSERT" -- an equivalent UPDATE followed by an INSERT.

For overall performance,, make sure you have...

100% not using a JOIN clause screams "noob".

But I see many more issues.

A serious one is not including a schema name before the table names. ...

Remove the comma from the last column name before the FROM:
...
hd--, --<--remove this comma
...
from icr_emp

You do need a separate codes table to do what you want.  But that table could be fully maintained automatically by triggers on the "master table".  That is, you wouldn't...

My preference has been to store the original sys.indexes and sys.index_columns rows rather than storing what you generated from them.  You can always generate it later if you need to,...

Not 100% sure of the specific MIN rules, but something like this should do it, and far more efficiently:

SELECT DISTINCT

ire.SourceId as...

Long shot, but I suppose it's possible that the original db was very physically fragmented on disk and the restore made the disk contiguous.  Very rare nowadays, esp. with modern...

Look into log space allocations.  You can trace them in different ways, including extended events. 

When a log file is extended, activities in the db must cease while...

How is the table clustered?  If you query the table by date, cluster it by date.  Then you won't have any issues reading historical data, and you could safely use...

It really does seem to be some efficiency mechanism in SQL to not use more than 5 bytes unless it's actually needed to store the specific value being saved.