Problem
I’m attempting to compare two SQL Server tables in order to verify some data. I’d like to get all of the rows from both tables that have data in one or the other. In essence, I want to point out all of the inconsistencies. In order to do so, I need to check three pieces of information: FirstName, LastName, and Product.
I’m new to SQL, and it appears like many of the answers I’m discovering are very complicated. I don’t need to be concerned about NULLs.
I began by attempting something similar to this:
SELECT DISTINCT [First Name], [Last Name], [Product Name] FROM [Temp Test Data]
WHERE ([First Name] NOT IN (SELECT [First Name]
FROM [Real Data]))
However, I’m having problems pushing this any further.
Thanks!
EDIT:
based on @treaschf’s response I’ve been attempting to use a variation of this query:
SELECT td.[First Name], td.[Last Name], td.[Product Name]
FROM [Temp Test Data] td FULL OUTER JOIN [Data] AS d
ON td.[First Name] = d.[First Name] AND td.[Last Name] = d.[Last Name]
WHERE (d.[First Name] = NULL) AND (d.[Last Name] = NULL)
However, I keep receiving 0 results, despite the fact that I know there is at least one row in td that is not in d.
EDIT:
Okay, I believe I’ve figured it out. At least in my brief testing, it appears to work satisfactorily.
SELECT [First Name], [Last Name]
FROM [Temp Test Data] AS td
WHERE (NOT EXISTS
(SELECT [First Name], [Last Name]
FROM [Data] AS d
WHERE ([First Name] = td.[First Name]) OR ([Last Name] = td.[Last Name])))
This will essentially inform me what is in my test data that isn’t present in my real data. That’s fine for what I’m trying to accomplish.
Asked by Casey
Solution #1
( SELECT * FROM table1
EXCEPT
SELECT * FROM table2)
UNION ALL
( SELECT * FROM table2
EXCEPT
SELECT * FROM table1)
Answered by erikkallen
Solution #2
Here are the entries that are present in table A but not in table B if you have tables A and B, both with column C:
SELECT A.*
FROM A
LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL
A full join, such as this, must be used to get all the differences with a single query:
SELECT A.*, B.*
FROM A
FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL
What you need to know in this scenario is that if a record exists in A but not in B, the columns from B will be NULL, and similarly, if a record exists in B but not in A, the columns from A will be null.
Answered by treaschf
Solution #3
I understand that this may not be a popular answer, but I agree with @Randy Minder that when a more complex comparison is required, a third-party tool should be used.
This particular scenario is simple, and no such tools are required, but it can quickly become complicated if you add more columns, databases on two servers, more complex comparison criteria, and so on.
There are plenty of these tools available, such as ApexSQL Data Diff or Quest Toad, and you may always try them out in trial mode.
Answered by Maisie John
Solution #4
You may use this SQL request to get all the differences between two tables, just like I did:
SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2
) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1
) AS T2
;
Answered by bilelovitch
Solution #5
The following is a simple version on @erikkallen’s solution that shows which table the row is in:
( SELECT 'table1' as source, * FROM table1
EXCEPT
SELECT * FROM table2)
UNION ALL
( SELECT 'table2' as source, * FROM table2
EXCEPT
SELECT * FROM table1)
If you encounter a problem,
therefore it could be beneficial to include
( SELECT 'table1' as source, * FROM table1
EXCEPT
SELECT 'table1' as source, * FROM table2)
UNION ALL
( SELECT 'table2' as source, * FROM table2
EXCEPT
SELECT 'table2' as source, * FROM table1)
Answered by studgeek
Post is based on https://stackoverflow.com/questions/2077807/sql-query-to-return-differences-between-two-tables