CODEWITHSUNDEEP
sqlsql-servert-sqlmerge
ChrisS
ChrisS

Reputation: 133

T-SQL Merge when matched not working

I have a temp table containing email traffic data between domains as follows:

[EmailId|SendingDomainId|SendingDomainName|RecipientDomainId|RecipientDomainName]

[500|600|abc.com|700|pqr.com]

[501|601|def.com|701|stu.com]

[501|601|def.com|700|pqr.com]

[502|600|abc.com|700|pqr.com]

That is:

I am trying to compile a report which contains totals of emails sent between domains, to produce the following:

[SendingDomainId|SendingDomainName|RecipientDomainId|RecipientDomainName|Total]

[600|abc.com|700|pqr.com|2]

[601|def.com|701|stu.com|1]

[601|def.com|700|pqr.com|1]

I am trying this MERGE statement but the UPDATE part is not working. I am ending up with a final table containing the same rows as the source table.

MERGE #DomainsChord_TrafficData as T
USING #DomainsChord_DomainEmails AS S
ON (S.SendingDomainId = T.SendingDomainId AND
    S.RecipientDomainId = T.RecipientDomainId)
WHEN MATCHED THEN UPDATE 
    SET T.TotalEmails = T.TotalEmails+1
WHEN NOT MATCHED BY TARGET THEN  
    INSERT (SendingDomainId, SendingDomainName, RecipientDomainId,
            RecipientDomainName, TotalEmails)
    VALUES (S.SendingDomainId, S.SendingDomainName, 
            S.RecipientDomainId, S.RecipientDomainName, 1);

Table #DomainsChord_TrafficData is an empty temp table before the merge. After the merge, it ends up with the same data as the source table (#DomainsChord_DomainEmails)

Is anyone able to spot where i am doing wrong?

Thanks in advance

Upvotes: 0

Views: 1148

Answers (2)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

You don't need a MERGE in your case. You need a simple SELECT with a GROUP BY clause as follows:

SELECT SendingDomainId, SendingDomainName, RecipientDomainId, RecipientDomainName
     , COUNT(*) AS Total
  FROM #DomainsChord_DomainEmails
 GROUP BY SendingDomainId, SendingDomainName, RecipientDomainId, RecipientDomainName;

OUTPUT

SendingDomainId SendingDomainName RecipientDomainId RecipientDomainName Total
--------------- ----------------- ----------------- ------------------- -----------
600             abc.com           700               pqr.com             2
601             def.com           700               pqr.com             1
601             def.com           701               stu.com             1

A MERGE statement is to merge data from two a source into a target. If your target (#DomainsChord_TrafficData) is empty, all data from source (#DomainsChord_DomainEmails) ends up in the target as you described.

Reference: MSDN MERGE T-SQL

Upvotes: 0

paulbarbin
paulbarbin

Reputation: 382

If the table is empty before hand, then there is no Match for the update to execute, it is a NOT MATCHED and therefore the insert runs.

Upvotes: 1

Related Questions