Union All but keep only duplicates from one table in T-SQL

Question

I have two table which I would like to union. I need to keep only the duplicates from one of the two tables. I tried to find it, but could not find it anywhere. Hope somebody can help.

For example: Table_1:

ID	Product	Amount
1	A	10
2	B	10
3	C	10

Table_2:

ID	Product	Amount
3	C	9
4	A	100
5	B	100

Desired result:

ID	Product	Amount
1	A	10
2	B	10
3	C	9
4	A	100
5	B	100

So always use the duplicates from table_2. In this example ID 3 is duplicate, so use the duplicate of table_2 with amount 9.

How to realize this with T-SQL? I used the code below:

Select * from Table_1 where Table_1.id != Table_2.id
Union All
Select * from Table_2 

But then I receive the error: 'The multi-part identifier "Table_2.ID" could not be bound.'

Answer 1

Use not exists:

Select t1.*
from Table_1 t1
where not exists (select 1 from table_2 t2 where t2.id = t1.id)
Union All
Select t2.*
from Table_2 t2;