CODEWITHSUNDEEP
sql-servert-sqlunion
user2447136
user2447136

Reputation: 189

Union all error must have equal number of expressions

I am trying to run this query but I am getting this error:

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

What am I doing wrong here? Please help.

SELECT      *
into       #xx 
From (
Select *
FROM #x 
union all  
select * from MartDB.DBO.BAW_AllSources_Stage1
) AAAsds

Upvotes: 4

Views: 15538

Answers (3)

dnoeth
dnoeth

Reputation: 60472

Both Selects must return the same number of columns and matching data types. If one Select returns an additional column, you can use NULL in the 2nd Select:

select integercol1, integercol2, varcharcol3, NULL
from t1
union
select integercol1, NULL,        varcharcol3, varcharcol4
from t2

. And if you got an error regarding non-matching data types, you can cast it like cast(NULL as TIME)

Upvotes: 2

Andrea
Andrea

Reputation: 12365

From Microsoft Docs:

The following are basic rules for combining the result sets of two queries by using UNION:

  • The number and the order of the columns must be the same in all queries.

More info here.

So you must guarantee that the select statements extract the same number of columns in the same order, so the best practice is to avoid * with union and union all.

Use explicit column names instead:

SELECT *
into       #xx 
From (
Select col_1, col_2, col_3 -- ... , col_n 
FROM        #x 
union all  
select col_1, col_2, col_3 -- ... , col_n 
from MartDB.DBO.BAW_AllSources_Stage1
) AAAsds

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

Your inner query part

Select *
FROM        #x 
union all  
select * 
from MartDB.DBO.BAW_AllSources_Stage1

has * in both queries participating in UNION.

Check and see if the both *s translate into same number of columns and data type in order.

Upvotes: 6

Related Questions