Union Two Tables and Overwrite Preexisting Rows

Question

I'm looking to union two tables together, but if there are any duplicate records where "Email" from Table 1 matches "Email" from Table 2, then data from Table 2 will be extracted. Is this function possible?

Table 1

Name | Email   | Status
A    | [email protected] | 1
B    | [email protected] | 2
C    | [email protected] | 1

Table 2

Name | Email   | Status
C    | [email protected] | 2
D    | [email protected] | 1
E    | [email protected] | 2

Resulting Table

Name | Email   | Status
A    | [email protected] | 1
B    | [email protected] | 2
C    | [email protected] | 2
D    | [email protected] | 1
E    | [email protected] | 2

Answer 1

One approach to this problem is to do a SELECT against table1 with a WHERE NOT IN against table2 to filter the rows selected from table1 so that none of the rows that exist in table2 will be part of that result -- then that result can be UNION'd against table2.

Here's an example (TableA and TableB in my code):

declare @TableA as Table ( Name VarChar(20), Email VarChar(20), Status INT );
declare @TableB as Table ( Name VarChar(20), Email VarChar(20), Status INT );

insert into @TableA ( Name, Email, Status ) values
  ( 'A', '[email protected]', 1 ),
  ( 'B', '[email protected]', 2 ),
  ( 'C', '[email protected]', 1 )

insert into @TableB ( Name, Email, Status ) values
  ( 'C', '[email protected]', 2 ),
  ( 'D', '[email protected]', 1 ),
  ( 'E', '[email protected]', 2 )

SELECT * FROM @TableA WHERE Email NOT IN ( SELECT DISTINCT Email FROM @TableB )
UNION 
SELECT * FROM @TableB