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

User · Accepted Answer

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

User · Answer

Not possible but if you try CTE  this would be the code   ALTER VIEW  dbo   VW PuntosDeControlDeExpediente  AS     WITH TEMP  RefLocal  IdPuntoControl  Descripcion       AS                SELECT                EX RefLocal               PV IdPuntoControl               PV Descripcion         FROM  dbo   PuntosDeControl  AS PV         INNER JOIN  dbo   Vertidos  AS VR ON VR IdVertido   PV IdVertido         INNER JOIN  dbo   ExpedientesMF  AS MF ON MF IdExpedienteMF   VR IdExpedienteMF         INNER JOIN  dbo   Expedientes  AS EX ON EX IdExpediente   MF IdExpediente           SELECT            Q1  RefLocal                IdPuntoControl      SELECT MAX IdPuntoControl  FROM TEMP WHERE  RefLocal    Q1  RefLocal  AND  Descripcion    Q1  Descripcion              Q1  Descripcion      FROM TEMP AS Q1     GROUP BY Q1  RefLocal   Q1  Descripcion  GO

User · Answer

Try creating another SQL view instead of a temporary table and then referencing it in the main SQL view  In other words  a view within a view  You can then drop the first view once you are done creating the main view

User · Answer

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

[sql-server] Is it possible to create a temporary table in a View and drop it after select?

Performance considerations

Examples related to sql-server

Examples related to tsql