CODEWITHSUNDEEP
sqlsql-serverunioninsert-into
odo22
odo22

Reputation: 610

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

Upvotes: 1

Views: 2988

Answers (5)

Jacek Wróbel
Jacek Wróbel

Reputation: 1222

Something like this could be the solution you seek.

SELECT Column1, Column2, 'A' AS Column3
FROM [Table A]
UNION
SELECT Column1, Column2, 'B' AS Column3
FROM (
    SELECT Column1, Column2 
    FROM [Table B]
        EXCEPT
    SELECT Column1, Column2 
    FROM [Table A]
) b

It's strange to me that it doesn't work. Your requirements can be translated as:

  1. Take all rows from set A and add column with value A.
  2. Take all rows from set B which are not in set A and and add column with value B.
  3. Return both results from step 1 and 2.

And this is what this query is doing.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You seem to want rows from a and then other rows from b. This is not exactly a union.

I would approach this as:

select a.column1, a.column2, 'a' as column3
from a
union all
select b.column1, b.column2, 'b' as column3
from b
where not exists (select 1 from a where a.column1 = b.column1);

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use UNION combine tableA and tableB then use outer join with CASE WHEN to make it.

WITH CTE AS (
  SELECT Column1,Column2
  FROM TableA
  UNION 
  SELECT Column1,Column2
  FROM TableB
)
SELECT t1.*,(case when t2.COLUMN1 is NOT null THEN 'A' ELSE 'B' END) Column3
FROM CTE t1 
LEFT JOIN  TableA  t2 on t1.COLUMN1 = t2.COLUMN1 and t1.COLUMN2 = t2.COLUMN2

sqlfiddle

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

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can use window function row_number()

with cte as
(
select column1,column2,'A' as column3 from tableA
union all
select column1,column2,'B' as column3 from tableB
) , cte2 as
(
select * , row_number() over(partition by column1,column2 order by column3 ) rn
from cte
) select column1,column2,column3 from cte2 where rn=1

demo link

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 2

Related Questions