Using a table variable in SQL Server

Question

I have designed a small query for table variable. This is the code:

DECLARE @TESTTABLE TABLE (ID INT, NAME VARCHAR(40))

INSERT INTO @TESTTABLE 
VALUES (1, 'WALLEY'), (2, 'BARRY'), (3, 'TIM'),
       (4, 'DICK'), (5, 'BRUCE')

My question is SQL allows use of alias for table variable like in the below query.

SELECT * 
FROM @TESTTABLE t 
INNER JOIN EMPLOYEE_DETAILS ON t.ID = BusinessEntityID

But you cannot use the actual table variable name like below:

SELECT * 
FROM @TESTTABLE 
INNER JOIN EMPLOYEE_DETAILS ON @TESTTABLE.ID = BusinessEntityID

The above query shows an error.

Does anyone have an answer for this? Would be really helpful.

Answer 1

In T-SQL, you need to use aliases when joining variable tables.

Reference : https://odetocode.com/articles/365.aspx (see section : Restrictions)

It should give you something like this :

SELECT * 
FROM @TESTTABLE tt 
INNER JOIN EMPLOYEE_DETAILS emp_d 
ON tt.ID = emp_d.BusinessEntityID

Answer 2

Use "[..]" to quote the table variable's name. This works for me:

SELECT * 
FROM @TESTTABLE INNER JOIN 
     EMPLOYEE_DETAILS
     ON [@TESTTABLE].ID = BusinessEntityID

That said, I would probably prefer to use an alias, or just use a CTE and forego the table variable altogether.