Is it possible to create a temporary table in a View and drop it after select?

Question

I need to alter one view and I want to introduce 2 temporary table before the SELECT.

Is this possible? And how can I do it?

ALTER VIEW myView AS   SELECT * INTO #temporary1  SELECT * INTO #temporary2  SELECT * FROM #temporary1 UNION ALL  SELECT * FROM #temporary1  DROP TABLE #temporary1 DROP TABLE #temporary2 

When I attempt this it complains that ALTER VIEW must be the only statement in the batch.

How can I achieve this?

Answer 1

No, a view consists of a single SELECT statement. You cannot create or drop tables in a view.

Maybe a common table expression (CTE) can solve your problem. CTEs are temporary result sets that are defined within the execution scope of a single statement and they can be used in views.

Example (taken from here) - you can think of the SalesBySalesPerson CTE as a temporary table:

CREATE VIEW vSalesStaffQuickStats AS   WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate)       AS       (             SELECT SalesPersonID, COUNT(*), MAX(OrderDate)             FROM Sales.SalesOrderHeader             GROUP BY SalesPersonID       )   SELECT E.EmployeeID,          EmployeeOrders = OS.NumberOfOrders,          EmployeeLastOrderDate = OS.MostRecentOrderDate,          E.ManagerID,          ManagerOrders = OM.NumberOfOrders,          ManagerLastOrderDate = OM.MostRecentOrderDate   FROM HumanResources.Employee AS E   INNER JOIN SalesBySalesPerson AS OS ON E.EmployeeID = OS.SalesPersonID   LEFT JOIN SalesBySalesPerson AS OM ON E.ManagerID = OM.SalesPersonID GO 

Performance considerations

Which are more performant, CTE or temporary tables?

Answer 2

You can achieve what you are trying to do, using a Stored Procedure which returns a query result. Views are not suitable / developed for operations like this one.