Creating temporary view from a temporary table in SQL Server

sql-server view temporary
Thunder picture Thunder · Aug 25, 2011 · Viewed 46.1k times · Source

I have a temporary table and I would like to create a temporary view over this temporary table.

Is it possible?

In following example I would like #Top10Records to be a view instead of a table so that I get 

select * into #Top10Records from (select top 10 * from #MytempTable)

Answer

DarylChymko picture DarylChymko · Aug 25, 2011

You can use a Common Table expression to do that:

WITH Top10Records  AS 
( 
 select top 10 * from #MytempTable
) 
SELECT * FROM Top10Records 
GO

Related questions

Is there a way to retrieve the view definition from a SQL Server using plain ADO?

I'm successfully extracting column definitions from databases hosted on a SQL server using the ADO Connection OpenSchema() call in its various incarnations so I can programmatically recreate those tables in another SQL database. So far, so good. The main interaction …

Read More
How can I check if a View exists in a Database?

I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists? EDIT: The DBMS being used is Microsoft SQL Server

Read More
Create a view with ORDER BY clause

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error: Msg 102, Level 15, State 1, Procedure TopUsers, …

Read More