Work around temporary table in SQL Server 2008 view

Question

I wrote a query in this format..

Select * INTO #xyz FROM ()

which I later want to use to create a view, as...

CREATE VIEW aaa
AS 
   Select * INTO #xyz FROM ()

but getting the following errors:

1. Incorrect syntax near the keyword 'INTO'.
2. Views or functions are not allowed on temporary tables

Can someone please suggest a workaround this? Is it possible to use temporary tables to create views?

Answer 1

You certainly cannot use a select into in a view. And a termp table is not approraite either. Use a derived table that is the equivalent of the temp table instead. Example:

Suppose you had:

select t1.id, t2.field1 into #temp from table1 t1
join Table2 t2 on t1.id = t2.id
where t2.somefield2 = 'mytest'

select t1.id, t2.field1, t1.field1
from mytable2 t1
join #Temp t2 on t1.id = t2.id
where t2.somefield = 'test'

Then you could use:

select t1.id, t2.field1, t1.field1
from mytable2 t1
join (select t1.id, t2.field1 
        from table1 t1
        join Table2 t2 on t1.id = t2.id
        where t2.somefield2 = 'mytest') t2 
    on t1.id = t2.id
where t2.somefield = 'test'

You could also usea a CTE