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

Answer 2

Union should be inside another select

SELECT * FROM(
    SELECT col1, col2 FROM test_table1
  UNION ALL
    SELECT col1, col2,col3 FROM test_table2
);

Result will be col1 and col2 the un matched columns are skipped

Answer 3

The best possible way to nullify the columns which were missing is to use null as column_name for which you want to see the null columns as like below :...

    SELECT id as var1, mgr_id as var2,name as var3 into exp_test1 FROM
 emp123    UNION ALL
      SELECT null as employeeid, null as departmentid,null as lastname FROM employee

Answer 4

I still suggest to go with @iliketocode.

Dynamic Columns Generate as per your requirement

This is just another option, but purely dynamic as you want (only its need some time to execute rather than above answer). To achieve the result without define each column, you can create dynamic query which automatically add column as null via loop.

The same issue I faced and due to short time, I was done as above. But today I fill to give the proper solution (which I not done at that time) and I created a sample for you as you want, I hope this will help you.

--create table t1 (col1 int , col2 int, col3 int)
--create table t2 (col1 int , col2 int, col3 int, col4 int)

--insert into t1 values (1,11,111), (2,22,222)
--insert into t2 values (1,11,111,1111), (2,22,222,null)

--Step 1 - Declaration of variable
Declare @NoOfColumnForUnion int = 5

declare @T1TableColumnList nvarchar(max) ='' ,@T2TableColumnList nvarchar(max) ='' , @colName nvarchar(500) , @test cursor

--Step 2 - Get the column list of first table i.e. t1 and store into @T1TableColumnList variable
set @test = cursor for 
                select name from syscolumns 
                where id = object_id('t1')                 

open @test
fetch next from @test into @colName
while @@fetch_status = 0 
begin
    set @T1TableColumnList = @T1TableColumnList + @colName + ','

    fetch next from @test into @colName 
end

set @T1TableColumnList = left( @T1TableColumnList , len(@T1TableColumnList )-1)

close @test
deallocate @test

--Step 3 - Get the column list of Second table i.e. t2 and store into @T2TableColumnList variable
set @test = cursor for 
                select name from syscolumns 
                where id = object_id('t2')                 

open @test
fetch next from @test into @colName
while @@fetch_status = 0 
begin
    set @T2TableColumnList = @T2TableColumnList + @colName + ','

    fetch next from @test into @colName 
end

set @T2TableColumnList = left( @T2TableColumnList , len(@T2TableColumnList )-1)

close @test
deallocate @test

--Step 4 - Check the length of column list to add null columns or remove columns
--First table check
Declare @T1lengthofColumnList int
set @T1lengthofColumnList =  (len(@T1TableColumnList) - len(replace(@T1TableColumnList, ',', '')) ) + 1

--add columns
if( @T1lengthofColumnList  < @NoOfColumnForUnion)
 Begin  
    While (@T1lengthofColumnList  < @NoOfColumnForUnion)
     Begin
        set @T1lengthofColumnList = @T1lengthofColumnList + 1
        Set @T1TableColumnList = @T1TableColumnList +  ', null col' + cast( @T1lengthofColumnList as varchar(10))
     End
 End
--remove columns
Else if( @T1lengthofColumnList  > @NoOfColumnForUnion)
 Begin
    While (@T1lengthofColumnList  > @NoOfColumnForUnion)
     Begin
        set @T1lengthofColumnList = @T1lengthofColumnList - 1       
        Set @T1TableColumnList = LEFT(@T1TableColumnList, LEN(@T1TableColumnList) - CHARINDEX(',',REVERSE(@T1TableColumnList))) 
     End
 End

--Second table check
Declare  @T2lengthofColumnList int
set @T2lengthofColumnList =  (len(@T2TableColumnList) - len(replace(@T2TableColumnList, ',', '')) ) + 1

--add columns
if( @T2lengthofColumnList  < @NoOfColumnForUnion)
 Begin  
    While (@T2lengthofColumnList  < @NoOfColumnForUnion)
     Begin
        set @T2lengthofColumnList = @T2lengthofColumnList + 1
        Set @T2TableColumnList = @T2TableColumnList +  ', null col' + cast( @T2lengthofColumnList as varchar(10))
     End
 End
--remove columns
Else if( @T2lengthofColumnList  > @NoOfColumnForUnion)
 Begin
    While (@T2lengthofColumnList  > @NoOfColumnForUnion)
     Begin
        set @T2lengthofColumnList = @T2lengthofColumnList - 1       
        Set @T2TableColumnList = LEFT(@T2TableColumnList, LEN(@T2TableColumnList) - CHARINDEX(',',REVERSE(@T2TableColumnList))) 
     End
 End

--Step 5 - create dynamic query and execute
DECLARE @template AS varchar(max)

SET @template = 'select ' + @T1TableColumnList + '  from t1 union all ' 
+ ' select ' + @T2TableColumnList + '  from t2 '

select @template 

EXEC (@template)


--drop table t1
--drop table t2