Geek asks and answers

T-SQL: how to join @variable tables

Possible duplicate:
T-SQL: how to join @variable tables (another attempt)

First: I use SQL Server 2008. In a complex algorithm that includes a lot of data, I use the technique of creating intermediate table variables:

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

Unfortunately, SQL Server does not support JOINning @variable tables; only joining the "true" tables in the database is allowed.

I could make a manual connection, for example

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

The result is an INNER JOIN, but for me this is wrong. The question is: how do FULL JOIN two @variable tables?

+3
source share
3 answers

SQL, ?

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
+10

, @tableVariable

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

- ? ( 100)

sp_dbcmptlevel 'database_name'

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

I'm not sure what you are asking, since join works 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
+2
source

More articles:


All Articles