Search code examples
sqlsql-serverunioninsert-into

SQL Union two tables and keep table name in a column

I am trying to UNION two tables whilst retaining information about which table the entry is from.

For example, given this input 

Table A
Column1   Column2
   0         X
   1         Y

Table B
Column1   Column2
   3         Z
   1         Y

I want to end up with this:

Table C
Column1   Column2 Column3
   0         X        A
   1         Y        A
   3         Z        B

I tried an INSERT INTO statement but I can't insert different text in Column3 without getting all the duplicates from e.g. Table 2

Solution

  • You want full outer join :

    SELECT COALESCE(a.col1, b.col1), COALESCE(a.col2, b.col2), 
       (CASE WHEN a.col1 IS NOT NULL 
             THEN 'A' 
             ELSE 'B' 
        END)
FROM tableA a FULL OUTER JOIN
     tableB b
     ON b.col1 = a.col1;