Use an alias, not the table variable’s name, as the column prefix. Put the variable in the FROM clause, assign it an alias, and qualify columns with that alias—for example, m.EmployeeID.
Can you use the table variable name as a column prefix?
In Microsoft’s documented T-SQL syntax, qualify a table variable’s columns with an alias. Microsoft Learn states that outside a FROM clause, table variables must be referenced by using an alias. Its join example names the variable @MyTableVar, aliases it as m, and refers to columns as m.EmployeeID and m.DepartmentID. See Microsoft Learn’s table (Transact-SQL) documentation.
Example: alias the variable in FROM
DECLARE @MyTableVar TABLE
(
EmployeeID int,
DepartmentID int
);
SELECT m.EmployeeID,
m.DepartmentID
FROM @MyTableVar AS m;Here, @MyTableVar identifies the table variable as the source in FROM. The alias m is the qualifier used before each column name. Do not substitute the variable’s @-prefixed name for the documented alias qualifier.
When the alias matters
Use an alias when a query needs qualified column references, especially in joins. Microsoft’s example uses the alias in a join condition. A query selecting columns from a table variable can also use unqualified column names, as in Microsoft’s simple FROM example; when you need a prefix, use the alias.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11#1 Best Overall
Scope
This guidance is for Microsoft T-SQL documentation covering SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Microsoft Fabric. It does not establish equivalent syntax rules for other database systems. The MicrosoftDocs source file records an editorial date of April 5, 2023: table (Transact-SQL) source file.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
