CODEWITHSUNDEEP
sql-serversql-server-2014
DmO
DmO

Reputation: 399

SQL Server Union All with one different column

I am using union all function inside two tables. Two tables are exactly the same except one column.

Here is my code:

SELECT x.InventoryTransTempID
    ,x.InventoryTransID
FROM (
    SELECT *
    FROM InventoryTransTemp

    UNION ALL

    SELECT *
    FROM InventoryTrans
    ) x

The only column which is different is the Identity column. In first table is called InventoryTransTempID and in Second InventoryTransID. Is there any way to inform sql server that if column name is InventoryTrans just renamed it to InventoryTransTempID.

My exception is:

Invalid column name 'InventoryTransID'.

Upvotes: 0

Views: 1068

Answers (2)

forpas
forpas

Reputation: 164064

The result of UNION ALL is all rows from both tables and the names of the columns are taken from the 1st query of the unioned queries.
So you can't have in the results both column names.
In this query:

select * from InventoryTransTemp
union all 
select * from InventoryTrans

the column's name will be InventoryTransTempID,
and in this query: 

select * from InventoryTrans
union all 
select * from InventoryTransTemp

the column's name will be InventoryTrans.
You can change the name if you alias it in the 1st query.

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5643

This error happens because when you perform union operation between two columns and try to get output in one column the name of column is the name of upper one column name as shown below.

Select 'a' as column1
union all
Select 'b' as column2

The output will be here

column1
-------
a
b

Here as per the above statement column2 is mixed with column1, So you can not get both the column name in outer query.

To check the column name you should first check the output of the inner query which is as below.

select * from InventoryTransTemp union all select * from InventoryTrans

Upvotes: 1

Related Questions