multiple select in view

Question

Hi Expert,

i wanted to store the data in temp table and then use in another to improve performance

i.e.

select col1,col2,col3 into #temp from table1

select col1,col2 ,table1.col3from view a join #temp on a. col1=b.col1

expected output: select col1,col2,col3 from view

getting error single select statement is allowed and How to delete the drop #temp table after view created

Answer 1

This is not a valid statement, you cannot use temp tables in SQL view, you can only use common table expressions as part of the view. Otherwise you may want to create a stored procedure for your purpose.

Answer 2

and how to tune this query using view to get early results as table 1 has huge data due to whcih wants to load in temp table

Answer 3

Hi @Shambhu Rai

the issue is there is one view in this query and another is table... how to update the view

At your request, I did a test.

select col1,col2 ,table1.col3 from view as a join #temp as b on a.col1=b.col1

This query has a result output. But the question is how to update this data into the view.

I wondered if I could transfer the data from this query to a new temp table again, and then update the view with the new table data. Like this.

select col1,col2,table1.col3 into #test from view as a join #temp as b on a.col1 = b.col1;
alter view view as select * from #test;

However, an error was reported. ‘Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables’.

So I wondered if I could replace the temporary table with a new table. But you mentioned above that temporary tables had to be used due to the large amount of data. It seems to be stuck in an endless loop.

Best regards,

Percy Tang