We help IT Professionals succeed at work.

comparing 2 tables

zachvaldez
zachvaldez asked
on
select statement to compare difference between 2 tables Compare the text difference between 2 columns, and also what is in one table not found in the other table
Comment
Watch Question

ste5anSenior Developer

Commented:
You can use the set operators EXCEPT and INTERSECT, when you need to compare by all attributes.
Otherwise use a FULL JOIN on the columns you want to compare.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
MS provides a free utility, tablediff.exe, that compares and can also provide a script to sync up the tables.  It's a command-line tool, so it takes getting used to, but it's ok once you get used to it.

Author

Commented:
can you walk me through by giving an example!of both suggested solution
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Is this a question, multiple questions, or multiple statements?

>Compare the text difference between 2 columns, and also what is in one table not found in the other table
That would be a LEFT JOIN.  Go onto images.google.com and search for 'SQL JOINS', and you'll see lots of pretty Venn diagrams with various queries involving two tables, the desired return set, and the T-SQL necessary to pull them off.

For example...
SELECT DISTINCT a.text_column
FROM TableA a 
   LEFT JOIN TableB b ON a.text_column = b.text_column  -- LEFT returns all row from TableA...
WHERE b.text_column IS NULL  -- ... where there is no matching TableB value

Open in new window

Author

Commented:
thanks! btw where can I find tablediff. exe just to explore and test it
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Just Google for it.  It's a free MS utility.

Author

Commented:
Here are the 2 tables

Table A
ProductNum
currentCost
CurrentPrice
MSRP
UPC

Table  B
ProductNum
OldCost
OldPrice
MSRP
UPC

I’m looking for
1) If Part is on Table A and Not in Table B, Mark is a New in a new column Status
2) If Part is not on Table A and is in Table B, mark it as expired in a column Status
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Which column is Part?  I see ProductNum and UPC..

Author

Commented:
that’s the part number