Is it possible to create a temporary table in a View and drop it after select?
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?
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.
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