Query to Return Missing Data

Question

I have two tables with almost exact structure, but one table always seems to not be updated appropriately. To show the issue, I have created two test tables that illustrate.

Declare @Test1 Table (card4 int, d1 date, amt float)
Declare @Test2 Table (card4 int, d1 date, amt float)
Insert Into @Test1 VALUES 
('4356', '01/01/2015', '12.24'), 
('4356', '01/01/2015', '44.12'), 
('1111', '01/01/2015', '55.10'), 
('2222', '01/01/2015', '23.12')
Insert Into @Test2 VALUES 
('4356', '01/01/2015', '12.24'), 
('4356', '01/02/2015', '11.11'),
 ('4356', '01/01/2015', '44.12')
Select * from @Test1
Select * from @Test2

From the sample data @Test1 is missing the amt 11.11 and I am not sure how to write a query that will pull all values from @Test1 and any values that are missing from @Test2

This is the data set that I want returned

card4 d1 amt 1111 1/1/2015 55.1 2222 1/1/2015 23.12 4356 1/1/2015 12.24 4356 1/1/2015 44.12 4356 1/2/2015 11.11

What would syntax be to achieve this?

• Forrest: You can use UNION between your two select statements to answer the question you asked. However, certain issues you mention (near identical tables, missing entries) point to other underlying problems.
• BellHopByDayAmetuerCoderByNigh: @Forrest - Yes there are underlying problems. These were 2 Spreadsheets that were received and imported into SQL that should have been identical...but were not.
• Randolph West: Excel problems are myriad. This could be a simple problem with the cell formatting in a certain row. Good luck! The UNION will solve this question's problem, as @Forrest suggested.

Answer 1

You can use SQL's EXCEPT clause. https://msdn.microsoft.com/en-us/library/ms188055.aspx

Values in the first table (the left query) that are not in the second (the right query) will be shown. You can change the table order to achieve the opposite.

select * from @Test1
except
select * from @Test2
card4 d1 amt
----------- ---------- ----------------------
1111 2015年01月01日 55.1
2222 2015年01月01日 23.12

• BellHopByDayAmetuerCoderByNigh: Maybe, I worded my ? incorrectly, I want all non duplicate results to be returned. This seems to return data that is in one table and not the other.
• datagod: I mis-understood the question. I'll leave this here for a bit then vote to delete it.

Answer 2

I am not very clear on your question however if I go by below comment

I want all non duplicate results to be returned

I would like to write code as below:

(Select * from @Test1
except
Select * from @Test2)
union
(Select * from @Test2
except 
Select * from @Test1
)

This would give you all non-duplicate value among these two tables:

Result

This can be achieved using CTE also however I feel except is good candidate here.

Kindly let us know if this is what you were looking for.