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?

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

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:

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.