You should cluster the staging table on the DM_KEY_HASH also, assuming you can compute the hash key as the row is being INSERTed into the staging table.  SQL is having to...

When you create a FK constraint, you can specify whether the key can be NULL or not.  If the creating code says it can't be set to NULL -- either...

If @value is a single value -- and it seems like it would have to be only a single value -- then wouldn't it be simpler and more efficient to...

Pass the RAND value in.  And, for efficiency, get rid of the local variables in the function.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

CREATE function...

You might want to test for lower case chars before doing the UPDATE, using any case-sensitive collation:

UPDATE CONTACT
SET COUNTRY = UPPER(COUNTRY)
WHERE COUNTRY...

You can also get that if you don't include the schema name when referencing tables.  For example:

SELECT ...
FROM table1

SQL will often generate a separate plan...

You can use a list of CROSS APPLYs to evaluate columns and concatenate values as you need to.  The great thing is that new aliases ("columns") created in a CA...

Check for INSERT triggers on the table being INSERTed into, just to be sure.  The error could be occurring in a trigger rather than for the main INSERT.

Sounds like you are looking only for a single match.  If so,you should skip the dbs after a match if found:

IF OBJECT_ID('tempdb.dbo.#stop_flag') IS NOT NULL