How to prove that ALL records from Query A are present in Table B for millions of records?-CodePudding

I have query written (in Microsoft SQL Server Management Studio V18) which does multiple inner joins to give result set with 3 columns: ZipCode, ID, Income.

This result set contains 118 Million records

I have Table B with 2 columns: ZipCode, ID

Table B contains 123 Million records

These 118M records are present in Table B and I want to prove that. How do I do this? I don't want another resultset that will display all these 118M records on the output console.

I can add first result set in a temp table but I am stuck after that. Ideally I would like to see something printed on the console that will say that "All the records from temp table are present in target table<Table_Name>"

If not, what could be an ideal way to prove that all these records are present in target table?

CodePudding user response：

so after say you have pushed your query results into temp table say #a, we can proceed like below

IF NOT EXISTS
(
SELECT TOP 1 1
FROM #a A
WHERE NOT EXISTS 
(
SELECT 1 FROM TableB B
ON A.ZipCode=B.ZipCode 
AND A.Id= B.ID
)
)

BEGIN
PRINT 'All the records from temp table are present in target table<Table_Name>'
END

CodePudding user response：

WITH 
TA AS (SELECT ...), --> first query
TB AS (SELECT ...)  --> second query
SELECT * FROM TA
EXCEPT
SELECT * FROM TB
UNION ALL
SELECT * FROM TB
EXCEPT
SELECT * FROM TA;

Then replace by your queries TA and TB.