When I declare a table variable and refer to its columns as @table_variable.column_name, I get the error message Incorrect syntax near '.'. However, when I replace @table_variable.column_name with column_name, the query executes correctly. How can I qualify the table variable's columns?
To qualify the table variable's columns, you have to assign an alias to the table variable, then use that alias to qualify the columns from that table variable. The following example declares a table variable, populates it with one row, provides the alias T to the table variable, then uses the alias T as the column qualifier:
DECLARE @mytable TABLE(col1 int) INSERT INTO @mytable VALUES(1) SELECT T.col1 FROM @mytable AS T
Using a table alias is often required when the table variable is involved in a join because you have to qualify columns in the table variable that have the same names as columns in the second table. The following code snippet uses a table alias in a join:
SELECT * FROM @mytable AS T1 JOIN T2 ON T1.key_col = T2.key_col