Compare data between two tables

Question

Post reply

Compare data between two tables

tchovanec 65734

SSCommitted

Points: 1740
More actions
August 3, 2010 at 9:42 am

#225646

I am trying to compare data between 2 tables. Does anyone know how I could go about this? I need to make sure that each line item matches. Thanks for any assistance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You may find these articles useful.

http://www.sql-server-tool.com/compare-two-tables.htm

http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 2

Also I find that SQL Data Compare by Red Gate is a great tool for not only doing the equivalent of those queries but also giving a GUI to see exactly what is different between the two tables and generating a script to make one look like the other. Of course, you have to buy that tool, but sometimes tools are worth the cost.

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 3

I also use Redgate SQL Compare and its a great tool.

WayneS SSC Guru Points: 95460 More actions · Answer 4

check out the EXCEPT and INTERSECT operators:

select <columnlist> from table1

EXCEPT

select <columnlist> from table2

will show you what's in table1 but not in table2

replacing EXCEPT with INTERSECT will show you what is in both.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 5

I do not think that using EXCEPT keyword will fulfill the requirement. As it will give you all the records from table1 which are not present in table2.

But what if there is any record which is present in table2 but not in table1 OR if the record is present in both tables but with different values in a column?

I do not agree that we could use EXCEPT to compare data between two tables.