UNION ALL on 3 tables with different number of columns in each table - how to do so efficiently

Question

I wanted to use UNION ALL on 3 different tables, merging them into one table by using SELECT INTO.

Table 1 ,2 and 3 has 15, 7 and 8 columns respectively.

So, was there a way for me to use UNION ALL and have table 2 and 3 default to a NULL for the missing columns, without individually classifying them as such?

For instance, I've been doing:

SELECT  NULL as [Company_Code], NULL as [Doc_Code], 
NULL as [Doc_Type], [H ID] as [Document_No] FROM [table_2] 
INTO BIG_TABLE
UNION ALL 
SELECT
[Document Company] as [Company_Code], [Document Company] as [Doc_Code], 
[Doc Type] as [Doc_Type], NULL as [Document_No]
FROM [table_3]

In this way, the number of columns match up, and I can UNION them.

However, I was wondering if there was a way to avoid the tedious mechanism to avoid inserting NULL for each column that was missing, and have that done automatically in one go ?

Thanks.

Answer 1

In short, no. Unioning result sets together must have the same number / data type of columns. If you wanted to have the remaining sets populate null, the simplest way to do this would be to do something like so-

select col1
, col2
, col3
, col4
from tbl1

union all

select null as col1
, null as col2
, null as col3
, null as col4
from tbl2