I know it is hard to you to give as most precise answer as you could do, and me too 🙁

However I appreciate much.

If I come up with a clear explanation why it happened I'll surely reply on this thread.

select * from deposits_daily_balance ddb

where ddb.val_date_d = cast('2012-07-24' as datetime) or ddb.reg_date_d = cast('2012-07-24' as datetime)

-- 0 records

select ddb.num_cue_s, count(*)

from ddb_temp_DDB ddb -- or from #ddb_temp_DDB ddb

group by num_cue_s

having count(*)>1

--0 records

primary key is composed of num_cue_s,val_date_d and reg_date_d

declare @t_date_d datetime

set @t_date_d = cast('2012-07-24' as datetime)

INSERT INTO deposits_daily_balance

SELECT

tdb.num_cue_s,

@t_date_d AS val_date_d,

@t_date_d AS reg_date_d,

val_date_balance_n,

recalc_int_n,

matured_int_n,

matured_bonus_int_n,

OD_matured_int_n,

int_accrued_n,

bonus_int_accrued_n,

OD_int_accrued_n,

tdb.int_mes_n,

accrued_fee_n,

paid_maintenance_fee_n,

put_on_reservation_n,

out_of_reservation_n,

tdb.od_limit_n,

tdb.od_fac_l,

cor_reg_n,

tdb.branch_s,

tdb.account_definition_id_n,

tdb.estado_s,

tdb.fee_offbalance_l,

AMF_last_payment_date_d,

AMF_last_accrual_date_d

-- this does not work

FROM #ddb_temp_DDB tdb

where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)

-- this does not work

-- this works

FROM ddb_temp_DDB tdb

where not exists (select 1 from deposits_daily_balance ddb_t where ddb_t.num_cue_s =tdb.num_cue_s and ddb_t.val_date_d = @t_date_d and ddb_t.reg_date_d = @t_date_d)

-- this works