How to compare 3 columns in 2 different databases?

Question

How to compare 3 columns in 2 different databases?

ccameron 64337

SSC Veteran

Points: 253
More actions
August 31, 2018 at 10:32 am

#367701

I have a Large (7 million) record database that I want to compare to a 50,000 record database and show only the ones in the 50,000 DB that do not exist in the larger one? There are only 3 columns that I want to look and I'm not sure what the best and/or easiest way to do this. I'm a little light on SQL and I'm running MS SQL 2012 on a Windows 2012 server on an isolated network.
Any input will be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99134 More actions · Answer 1

One way would be to use EXISTS. There's not a lot of data to work on here, so the below is Pseudo-SQL:

SELECT *
FROM SmallDB.dbo.YourSmallTable YST
WHERE NOT EXISTS (SELECT 1
       FROM LargeDB.dbo.YourLargeTable YLT
       WHERE YST.IDColumn = YTL.IDColumn
       --AND Some other criteria..?
       );

If there's only 3 columns you care about, then change the * to the relevant columns, rather than returning them all.

Alternatively, another way is using a LEFT JOIN, and checking for a NULL:

SELECT *
FROM SmallDB.dbo.YourSmallTable YST
  LEFT JOIN LargeDB.dbo.YourLargeTable YLT ON YST.IDColumn = YTL.IDColumn
                --AND Some other criteria..?
WHERE YTL.IDColumn IS NULL;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ccameron 64337 SSC Veteran Points: 253 More actions · Answer 2

Thom, Thank you for the input. I decided to import the smaller database into a separate table. My thinking there is that it would be easier since both now exist in the same database. Looking at your code snippet, I could then drop the database designations and just say from table, correct?
Select *
FROM SmallTable YST
Where NOT EXISTS (SELECT 1
FROM LargeTable YLT
   WHERE YST.Column1 = YTL.Column1
   AND YST.Column2 = YTL.Column2
AND YSL.Column3 = YTL.Column3
   );

andycadley SSCertifiable Points: 5296 More actions · Answer 3

You could just do:

Select cola, colb, colc From SmallTable Except Select cola, colb, colc From LargeTable