SQL Server Portal

Union [ALL] operator is one of the frequently used operators in the SQL Server scripting. We usually use it to combine multiple query result set together. But it has one of the limitations that all the queries column must be equal. But there are multiple ways to by pass this limit.

Let’s discuss this in detail:

Message Number: 205

Severity : 16

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

Error Generation:
Let me create two sample tables & insert some sample data in it to demonstrate this error and its solutions.

USE tempdb
GO
--Create first table
CREATE TABLE tbl_sample_1
(
 [ID] int,
 [Name] varchar(50),
 [Modified Date] datetime
)
GO
--Insert few records in first table
INSERT INTO tbl_sample_1 VALUES (1,'SQL Server 2005','2005-01-01')
INSERT INTO tbl_sample_1 VALUES (2,'SQL Server 2008','2008-01-01')
GO
--Create second table
CREATE TABLE tbl_sample_2
(
 [ID] int,
 [Name] varchar(50),
)
GO
--Insert few records in second table
INSERT INTO tbl_sample_2 VALUES (1,'SQL Server 2012')
INSERT INTO tbl_sample_2 VALUES (2,'SQL Server 2014')
GO

Let me apply union operator on both tables. Given below is the script :

USE tempdb
GO
SELECT [ID],[Name],[Modified Date]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name]
FROM tbl_sample_2
--OUTPUT

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Ooopps…… I am unable to apply union operator on above tables and the reason behind this is that both table’s have different number of columns.

Resolution:
Given below are the three different resolutions that I usually use to fix this error.

Solution 1 :
In this solution you can remove the additional column from the query. This additional column can be in any query. In our sample query we have additional column [Modified Date] in query 1. So lets remove it.

USE tempdb
GO
SELECT [ID],[Name]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name]
FROM tbl_sample_2
--OUTPUT

Solution 2 :
In this solution you can leave the additional column in the query but add the same addtional column in query 2 with the value of NULL. Given below is the script.

USE tempdb
GO
SELECT [ID],[Name],[Modified Date]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name],NULL As [Modified Date]
FROM tbl_sample_2
--OUTPUT

Solution 3 :
In this solution you can leave the additional column in the query but add the same additional column in query 2 with the DEFAULT value. Be sure that the DEFAULT value is compatible with the additional column. Given below is the script.

USE tempdb
GO
SELECT [ID],[Name],[Modified Date]
FROM tbl_sample_1
UNION ALL
SELECT [ID],[Name],GETDATE() As [Modified Date]
FROM tbl_sample_2
--OUTPUT

Read Full Post »