What you're looking for is essentially an UPSERT procedure, or a MERGE.

Here's one way of doing it:

CREATE TABLE #ActiveRecords

(

fund_id INT PRIMARY KEY,

[name] VARCHAR(100)

)

INSERT INTO #ActiveRecords (fund_id, [name])

SELECT

fund_id,

[name]

FROM Fund

WHERE GETDATE() BETWEEN val_start_date AND val_end_date

-- SET INACTIVE ALL RECORDS WHICH DON'T EXIST --

UPDATE f

SET f.active_version_id = NULL,

f.is_active_version = 0

FROM Fund f

LEFT JOIN #ActiveRecords a ON f.[name] = a.[name]

WHERE a.[name] IS NULL

-- UPDATE RECORDS WHICH DO EXIST --

UPDATE f

SET f.active_version_id = (CASE WHEN f.fund_id = a.fund_id THEN NULL ELSE a.fund_id END),

f.is_active_version = (CASE WHEN f.fund_id = a.fund_id THEN 1 ELSE 0 END)

FROM Fund f

JOIN #ActiveRecords a ON f.[name] = a.[name]

I would recommend that you find a better way of being able to identify common records than by using the name. You should have one table that has the information about the different funds, and another table that has the information about the different instances of the funds.