CODEWITHSUNDEEP
sqlsql-servermergecount
Migz
Migz

Reputation: 113

SQL - Using merge and counting the matched and non matched results

So if I were to use the following query I'd like to find out how many rows have been matched

MERGE (targetTable) AS t 
USING (sourceTable) AS s
ON t.ID = s.ID
WHEN MATCHED THEN
  (some statements)
WHEN NOT MATCHED BY SOURCE THEN
  (some statements)
WHEN NOT MATCHED BY TARGET THEN
  (some statements)

So I'd like to know the count for

I know you can count the output, but if I recall correctly, the outputs only account for insert, update and delete.

Upvotes: 1

Views: 182

Answers (1)

gofr1
gofr1

Reputation: 15997

You can run something like this before merging:

SELECT  SUM(CASE WHEN t.ID = s.ID THEN 1 ELSE 0 END) as [Matched],
        SUM(CASE WHEN s.ID IS NULL THEN 1 ELSE 0 END) as [NotMatchedByTarget],
        SUM(CASE WHEN t.ID IS NULL THEN 1 ELSE 0 END) as [NotMatchedBySource]
FROM targetTable AS t 
FULL OUTER JOIN sourceTable AS s
    ON t.ID = s.ID

Upvotes: 1

Related Questions