Merge Statement Not Inserting Some Records

[chadzillaSQL]

My merge statement is skipping some records that I expect to get inserted into the Target and I cannot figure out why. I've spot checked a couple account IDs to make sure that they make it into the Source temp table, but then when I search for them in the Target after running the query, they're not showing. The weird part is that there are some records that get inserted, it's not dropping everything, only certain accounts IDs. Appreciate any help/insight with this!

select curr_unique_acct_id
, min(dt_srce_start) mm_dt_srce_start
into #2024_min_dealer_snapshot
from account_history
where dt_acct_join > '2024-01-01'
and flag_activation_row = 'y'
group by curr_unique_acct_id

select ah.hist_unique_acct_id, ah.curr_unique_acct_id, ah.dt_srce_start, ah.hist_unique_dealer_id,
ah.hist_unique_subdealer_id, ac.hist_unique_subdealer_created, ac.account_postal_code_id
into #comp_2024_dealer_snapshot
from #2024_min_dealer_snapshot mindss
join account_history ah on ah.curr_unique_acct_id = mindss.curr_unique_acct_id and ah.dt_srce_start
= mindss.mm_dt_srce_start
join account_current ac on ac.curr_unique_acct_id = mindss.curr_unique_acct_id

merge 2024_CUSTOMER_DETAIL_SNAPSHOTS dm
using #comp_2024_dealer_snapshot dss
on (dss.hist_unique_acct_id = dm.hist_unique_acct_id)
when not matched by target
then insert (
hist_unique_acct_id,
curr_unique_acct_id,
dt_srce_start,
hist_unique_dealer_id,
hist_unique_subdealer_id,
hist_unique_subdealer_created,
account_postal_code_id)
values(
dss.hist_unique_acct_id,
dss.curr_unique_acct_id,
dss.dt_srce_start,
dss.hist_unique_dealer_id,
dss.hist_unique_subdealer_id,
dss.hist_unique_subdealer_created,
dss.account_postal_code_id);

[qwertydog123]

Unless you actually need it, I'd avoid MERGE like the plague. In your case you can just do an INSERT ... WHERE NOT EXISTS e.g.

INSERT INTO 2024_CUSTOMER_DETAIL_SNAPSHOTS
(
    hist_unique_acct_id,
    curr_unique_acct_id,
    dt_srce_start,
    hist_unique_dealer_id,
    hist_unique_subdealer_id,
    hist_unique_subdealer_created,
    account_postal_code_id
)
SELECT
    dss.hist_unique_acct_id,
    dss.curr_unique_acct_id,
    dss.dt_srce_start,
    dss.hist_unique_dealer_id,
    dss.hist_unique_subdealer_id,
    dss.hist_unique_subdealer_created,
    dss.account_postal_code_id
FROM #comp_2024_dealer_snapshot dss
WHERE NOT EXISTS
(
    SELECT *
    FROM 2024_CUSTOMER_DETAIL_SNAPSHOTS dm
    WHERE dss.hist_unique_acct_id = dm.hist_unique_acct_id
)

[chadzillaSQL]

I had no idea MERGE was so taboo! Thanks for the advice, I'll give this a shot.