CODEWITHSUNDEEP
sqlsql-server-2008merge
Bas
Bas

Reputation: 607

SQL Server MERGE, if exist then update else insert

I've a table called data with columns ip, report_date, group, value. The primary key is ip, report_date and group together.

When the table is empty and I run the statement below there is nothing inserted. What's wrong with my statement?

When there is a match the record is updates according plan...

MERGE bc_data2 AS Target
USING (SELECT ip, report_date, group, value FROM bc_data2 As b
WHERE b.ip = '1.1.1.2'
AND b.report_date = '2/29/2012'
AND b.group = 'EPO-Client-Update') AS Source
ON (Target.ip = Source.ip
AND Target.frequency = Source.frequency
AND Target.report_date = Source.report_date
AND Target.service = Source.service
AND Target.proxy_service = Source.proxy_service
AND Target.proxy = Source.proxy
AND Target.service_group = Source.service_group)
WHEN MATCHED THEN
    UPDATE SET Target.value = Target.value + 1
WHEN NOT MATCHED BY Target THEN
    INSERT (ip, report_date, group, value)
    VALUES ('1.1.1.2', '2/29/2012', 'EPO-Client-Update', 119437142);

Upvotes: 1

Views: 11600

Answers (2)

Patrick Kelly
Patrick Kelly

Reputation: 33

I've found that NULLs don't compare well in the ON clause of the MERGE statement. However, converting NULLs to blanks is a valid workaround. I would modify the the ON statement to look something like this.

ON (ISNULL(Target.ip, '')           = ISNULL(Source.ip, '')
AND ISNULL(Target.frequency, '')    = ISNULL(Source.frequency, '')
AND ISNULL(Target.report_date, '')  = ISNULL(Source.report_date, '')
AND ...)

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You don't add any rows because your using clause is not returning any rows. Put your constants in the using clause with column aliases and use the fields in the when not matched

Something like this with a couple of fields removed simplicity.

merge bc_data2 as T
using (select '1.1.1.2' as ip,
              '2012-02-29' as report_date,
              1194370142 as value) as S
on T.ip = S.ip and
   T.report_date = S.report_date
when matched then
  update set T.value = T.value + 1
when not matched then
  insert (ip, report_date, value) 
    values(ip, report_date, value);

Upvotes: 4

Related Questions