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?
This question is related to
sql-server
tsql
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
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.
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.
Source: Stackoverflow.com