Union two tabls with different columns

Question

Hi Everyone,

I have a table A that has 205 columns and table B that has 160 columns. Some of the columns in two tables matches but there are differnces. is there a simple way to do a union of two tables and get null for column that doesnt belong in either table. For fewer numbr of columns this is an easy task but but tables with more than 100 columns can be manual. I am trying to find a better way to do this.

select col1, col2, col3............... from table1

union

select col1, col2, col3, col5 .... from table2

I know column order has to be the same in 2 queries. But I want to find a automated way to write this query using sql. 

Any good ideas to do this?

Thanks.

Answer 1

Hi @Rohit Kochar
Try this:

select col1, col2, col3,null as col5 ,......from table1  
union  
select col1, col2, col3, col5 .... from table2  

Best regards,
LiHong

Answer 2

to use UNION both queries must hold the same columns

i would suggest you fill in the blanks on either side with "null as colname", and any other "matching" columns you take as a standard name for example

select
col1.col1name1 as colname1
,col1.col1name2 as colname2
, null as colname3

from schema1.table1 as col1

union

select
col2.col2name1 as colname1
,null as colname2
,col2.col2name3 as colname3

from schema2.table2 as col2

you could use the "sys" or "information_schema" schema to dynamically build out the table query on both sides - finding matches and non matches on each side then flagging in for a null as value

to get you started

---

select
@table1 = ""
,@table2 = ""

select
s.name as schema_name
,t.name as table_name
,c.name as column_name
FROM sys.all_objects t

LEFT OUTER JOIN sys.schemas s
ON t.schema_id = s.schema_id

LEFT OUTER JOIN sys.all_columns c
ON t.object_id = c.object_id

LEFT OUTER JOIN sys.systypes st
ON c.system_type_id = st.xusertype

INNER JOIN sys.types tp
ON c.user_type_id = tp.user_type_id

WHERE schema_name(t.schema_id) not in
(
'INFORMATION_SCHEMA'
,'sys'
)
----debug, remove to run for larger group
AND t.type = 'U'
AND (t.name = @table1 or t.name = @table2)

ORDER BY
t.schema_id
,t.object_id
,c.column_id
;

Answer 3

Sélectionner les colonnes
Trier par noms/valeurs

Answer 4

Thank you everyone!

I came with this below script:

with A as
(
SELECT c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name like '%tableA%' --172
),
B as
(
SELECT c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name like '%tableB%' ---186
),

select A.ColumnName, B.ColumnName
from A left outer join B
on A.ColumnName = B.ColumnName

Now i can see which columns do not belong in table B.

Answer 5

DELETEME_DELETEME ;)