How to join table variables?

Question

Possible Duplicate:
T-SQL: How to join @variable tables (another try)

First: I'm using SQL Server 2008. In a complex algorithm that involves a lot of data, I have been using a technical of creating intermediate table variables:

DECLARE @table AS TABLE (Col1 INT, Col2 VARCHAR(100))

Unfortunately, SQL Server does not support JOIN'ing @variable tables, it is only allowed to join "true" tables, those in the database.

I could do the "manual" join, like

FROM @table1 t1, @table2 t2
WHERE t1.Id = t2.Id

This results in a INNER JOIN, but this is wrong for me. The question is: How do FULL JOIN two @variable tables?

Since at least 3 people disagree with your "SQL Server does not support...", perhaps you could post what you tried and the error message you received, and we may be able to provide more enlightenment. — Damien_The_Unbeliever, Jun 09 '11 at 13:26

Ray · Answer 1 · 2011-06-09T13:15:41.960

13

What do you mean by SQL doesn't support Joining table variables?

It works for me

DECLARE @table1 AS TABLE (Col1 INT, Col2 VARCHAR(100))
DECLARE @table2 AS TABLE (Col1 INT, Col2 VARCHAR(100))

SELECT *
FROM @table1 t1
FULL JOIN @table2 t2 on t1.Col1 = t2.Col1

edited Jun 09 '11 at 13:15

answered Jun 09 '11 at 13:09

Ray

45,695
27
126
169

score 2 · Answer 2 · answered Jun 09 '11 at 13:08

You should be able to do a join using an @tableVariable

SELECT * 
FROM table1 t
FULL JOIN @tableVariable tv
ON (tv.col = cnc.col)

Could it have anything to do with your compatability setting? (mine is at 100)

sp_dbcmptlevel 'database_name'

ALTER DATABASE database_name 
    SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

score 2 · Answer 3 · answered Jun 09 '11 at 13:12

I'm not sure what you're asking, as joining works just fine for table variables. See this example:

declare @table as table (Col1 int, Col2 varchar(100))
declare @table2 as table (Col1 int, Col2 varchar(100))

insert into @table
select 1, 'A'
union all
select 1, 'C'
union all
select 1, 'D'

insert into @table2
select 2, 'A'
union all
select 2, 'B'
union all
select 2, 'D'
union all
select 2, 'E'

select
    *
from
    @table t1 full outer join
    @table2 t2 on t1.Col2 = t2.Col2

select
    *
from
    @table t1 left join
    @table2 t2 on t1.Col2 = t2.Col2

select
    *
from
    @table t1 right join
    @table2 t2 on t1.Col2 = t2.Col2

select
    *
from
    @table t1 join
    @table2 t2 on t1.Col2 = t2.Col2

How to join table variables?

3 Answers3

Linked