Error in SQL Server when performing UNION on 2 tables

Question

I wanted to add one table below another one in SQL Server.

This is my code:

CREATE TABLE #main_table 
( Scenarios float ,Attribute VARCHAR(50) ,Value float ); 

insert INTO  #main_table  
select * 
FROM 
( SELECT *
FROM #table1
UNION ALL 
SELECT *
FROM #table2) a

But I get an error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists

Anyone would know what may be the issue?

Answer 1

You can try below - as union requires equal no of columns in both data set

insert INTO  #main_table  
select * 
FROM 
( SELECT Scenarios,Attribute ,Value
FROM #table1
UNION ALL 
SELECT Scenarios,Attribute ,Value
FROM #table2) a

Answer 2

The columns returned by

SELECT * FROM #table1

and the columns returned by

SELECT * FROM #table2

must be of equal number and have the same type.

Answer 3

use explicit column for union

 insert INTO  #main_table (Scenarios,Attribute,Value) 
    select * 
    FROM 
    ( SELECT col1,col2,col3
    FROM #table1
    UNION ALL 
    SELECT col1,col2,col3
    FROM #table2) a

Union follow below condition

• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order