Merge with Max value of another table

Question

I have two tables: DestinyTablaBQ: Has a column with the maximum value of PERIODO in SourceTabla and other data, like this:

    PERIODO    DATE 
    202001     01/01/2020
    202002     01/01/2020
    202003     01/01/2020
    202004     01/01/2020

And a Source table, that has a few million records like this:

    ANOMES
    202001
    202001
    202001
    202002
    ...
    202005    --> Missed in the DestinyTablaBQ table, Needs to be inserted or updated`

I need to create a SQL to insert or update into the DestinyTableBQ, using the max ANOMES value from the source.

I've created the following SQL, using Merge:

    MERGE [dbo].[DestinyTableBQ] BQ USING SourceTable T
    ON 
    BQ.PERIODO=MAX(T.ANOMES)
    WHEN NOT MATCHED
        THEN 
        INSERT (PERIODO,Date) 
        VALUES(MAX(T.ANOMES),'');`

But I'm geting the following error:

"An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."

Any idea about how could I use merge to do this? Or how should I do this without merge?

Thanks!!

Answer 1

You don't really need the merge syntax for this, since all you want is to do is insert a value if it's not alreay there. I think this would be simpler expressed with insert ... select ... where not exists:

insert into DestinyTablaBQ (periodo)
select s.max_anomes
from (select max(anomes) max_anomes from sourceTable) s
where not exists (
    select 1 from DestinyTablaBQ bq where bq.periodo = s.max_anomes
)

If you wanted to use a merge query, you would need to aggregate in a subquery first:

merge DestinyTableBQ bq
using (select max(anomes) max_anomes from sourceTable) s
on (bq.periodo = s.max_anomes)
when not matched then insert (periodo) values(s.max_anomes)