|
|
Error Message
Server: Msg 263, Level 16, State 1, Line 1
Must specify table to select from.
Causes
The SELECT statement retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as follows:
|
[ WITH <common_table_expression> ]
SELECT <select_list> [ INTO <new_table> ]
[ FROM <table_source> ]
[ WHERE <search_condition> ]
[ GROUP BY <group_by_expression> ]
[ HAVING <search_condition> ]
[ ORDER BY <order_expression> [ ASC | DESC ] ]
As can be seen from this main clause summary of the SELECT statement, only the SELECT clause is required together with the <select_list> while all others are optional. The <select_list> is the list of columns to be selected for the result set. The <select_list> is a series of expressions separated by commas. If all columns are to be returned from all the tables or views in the FROM clause, the * can be used as a short-cut. Here’s an example of a SELECT statement that will return all columns and all rows from the [dbo].[Users] table
SELECT * FROM [dbo].[Users]
When specifying * as the <select_list> in a SELECT statement, the FROM clause must also be specified, otherwise, this error message will be encountered as shown in the following SELECT statement:
SELECT *
Msg 263, Level 16, State 1, Line 1
Must specify table to select from.
A different error message will be generated if the FROM clause is specified but without specifying a table name, as can be seen in the following:
SELECT * FROM
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FROM'.
Similarly, instead of the * in the <select_list>, specifying a column name in the SELECT statement without the FROM cause will generate the following error:
SELECT [UserID]
Msg 207, Level 16, State 1, Line 1
Invalid column name 'UserID'.
And specifying a column name with just the FROM statement without a table or view will generate the following error:
SELECT [UserID] FROM
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FROM'.
Solution / Work Around:
As the error message suggest, to avoid this error from happening simply specify the table name or the view name when using the * in the <select_list> in a SELECT statement:
SELECT * FROM [dbo].[Users]
The SELECT statement can be used to return a row of data without including the FROM clause by specifying constant values for each column of data to return:
SELECT 1 AS [UserID], 'Mickey' AS [FirstName], 'Mouse' AS [LastName]
Multiple rows of data can be returned by a SELECT statement without the FROM clause with the use of the UNION ALL operator:
SELECT 1 AS [UserID], 'Mickey' AS [FirstName], 'Mouse' AS [LastName]
UNION ALL
SELECT 2 AS [UserID], 'Donald' AS [FirstName], 'Duck' AS [LastName]
UNION ALL
SELECT 3 AS [UserID], 'Minnie' AS [FirstName], 'Mouse' AS [LastName]
|