SQL Server
Error Messages - Msg 205
Error Message:
Server: Msg 205, Level 16, State 1, Line 2
All queries in an SQL statement containing a
UNION
|
Lets SEE AN EXAMPLE:
CREATE TABLE [dbo].[TEST_TABLE] (
[TESTID] INT,
[FirstName]
VARCHAR(50),
[LastName]
VARCHAR(50),
[GRADE] VARCHAR(50)
)
CREATE TABLE [dbo].[OLDTEST_TABLE] (
[TESTID] INT,
[FirstName]
VARCHAR(50),
[LastName] VARCHAR(50),
[GRADE] VARCHAR(50),
[Dates] DATETIME
)
To get a list of all names of the company, previous and past, you would
do a UNION ALL of both tables, as follows:
SELECT * FROM [dbo].[TEST_TABLE]
UNION ALL
SELECT * FROM [dbo].[OLDTEST_TABLE]
But since the [dbo].[OLDTEST_TABLE] table has an extra column,
[Dates], the following error is encountered:
Server: Msg 205, Level 16, State 1, Line 2
All queries in an SQL statement containing a UNION
operator
must have an equal number of expressions in their
target lists.
Solution/Workaround:
When performing a UNION or UNION ALL on two tables that do not have the
same number of columns, you have to specify each column in the SELECT clause
instead of doing a SELECT *. Aside from this, for the table that has a
lesser number of columns, you have to provide a filler for the missing column
or columns, such as NULL values or empty strings for VARCHAR columns and 0 for
numeric columns.
To avoid the error in the example above, here’s how the SELECT statement
will look like:
SELECT [TESTID], [FirstName], [LastName],
[GRADE], NULL AS [Dates]
FROM [dbo].[TEST_TABLE]
UNION ALL
SELECT [TESTID], [FirstName], [LastName],
[GRADE], [Dates]
FROM [dbo].[OLDTEST_TABLE]
Since the [dbo].[TEST_TABLE] table contain current NAMES, it doesn’t
have a column for the Dates. To overcome the error, a NULL
value is returned for that column
No comments :
Post a Comment