joining a table from a different database to a table variable

Question

I am working on a query that will join a table from one database to a table variable on a different database. However every time I try to run this, I get an error that says I need to declare the table variable, but it is already declared. Please help!

declare @Employee Table
(
    agt_responsible int, 
    employee_last_name varchar(50), 
    employee_first_name varchar(50),
    #_of_Compliments int, 
    DEBIT_Count int,
    DEBIT_POTENTIAL_AMT INT, DEBIT_TNT_AMT int,
    #_of_Value_Adds int,
    amt_of_Value_Adds int
) 

INSERT INTO @Employee
Exec proc_performance_AGT_roi '2015-05-01','2015-05-30'

SELECT * 
from omadb08.TANDT_EMPLOYEE.dbo.ADP_EMPLOYEE 
     INNER JOIN @Employee 
     ON @Employee.agt_responsible = OMADB08.TANDT_EMPLOYEE.DB0.ADP_EMPLOYEE.EMPLOYEE_ID

Answer 1

Due to how queries are parsed, when you use the table variable in the join you need use quoted identifiers (either [] or "") or an alias so change this part:

INNER JOIN @Employee 
ON @Employee.agt_responsible

to either:

INNER JOIN @Employee e
ON e.agt_responsible

or

INNER JOIN @Employee 
ON [@Employee].agt_responsible

or

INNER JOIN @Employee 
ON "@Employee".agt_responsible

I would use the alias option as I think it makes the query easier to read.