57

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
pencilCake
  • 51,323
  • 85
  • 226
  • 363

4 Answers4

107

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?

Marek Grzenkowicz
  • 17,024
  • 9
  • 81
  • 111
  • 12
    The problem is though, depending on what you're trying to do, is that the CTE is re-evaluated for every record in the SELECT. This still can have a huge performance hit. Would love a way for CTE to run "once" and the results used over and over, like a temporary table – Fandango68 Jun 30 '20 at 06:49
  • 2
    @Fandango68 Good point! I have just linked https://stackoverflow.com/a/26205087/95 for everyone who wants to analyze it deeper. – Marek Grzenkowicz Jun 30 '20 at 07:18
  • Well the good thing about views is that performance is not really an issue as they usually run the background so there is no need for temp tables as CTEs can still do the job, just a lot slower. Now if the CTEs are timing–out and the view cannot refresh then yes we do run into another issue there. In this case, it is better to add a view within a view – K. Ventura Jul 13 '20 at 00:06
  • 1
    @Fandango68 is right, I compared the CTE approach to INNER JOIN using a table with 20k records, joining the table to itself to filter out partially duplicate records. The CTE is way slower (12 sec) as the INNER JOIN approach (4 sec). – Aileron79 Jul 14 '22 at 08:50
5

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.

Attie Wagner
  • 1,312
  • 14
  • 28
Emir Akaydın
  • 5,708
  • 1
  • 29
  • 57
  • 6
    Be warned: creating a temporary table inside a stored procedure will cause that procedure to be **recompiled** (since the schema changes), and filling it with data might cause a **second recompile** (since statistics changed). This is not a very good approach..... – marc_s Nov 24 '11 at 11:52
  • My answer is for if it's ok to use views in such cases. I'm not trying to give the most optimized solution here. My answer applies to any case with more than a single SELECT query. – Emir Akaydın Nov 24 '11 at 12:00
  • 1
    I don't see why it's "not a very good approach" just because your Stored Proc gets recompiled twice... What's the problem with that exactly? – Geoff Griswald Nov 26 '19 at 11:52
2

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
tavalendo
  • 857
  • 2
  • 11
  • 30
Ángel Ibáñez
  • 329
  • 1
  • 6
  • 2
    `WITH TEMP` is defining a CTE, not a temporary table. – Russ Sep 25 '18 at 21:14
  • Ok yes, but is the same functionallity – Ángel Ibáñez Sep 26 '18 at 11:39
  • 2
    Mostly, but not exactly. For example, if you are trying to steer the planner in a certain direction by forcing the temp table creation. The planner will do whatever it feels like with whatever the CTE describes (usually a good thing, but not always). – Russ Sep 26 '18 at 16:45
  • Can you explain this more concretely? Thanks – Ángel Ibáñez Sep 27 '18 at 11:47
  • @ÁngelIbáñez This is not what the OP is asking. I would change your answer to something like: "No, you can't but in CTE it looks like this...." – tavalendo Nov 07 '18 at 09:31
0

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.

K. Ventura
  • 91
  • 2
  • 14