Create VIEW using temp table

Question

So I'm tasked with exporting a dataset to an excel file weekly. I'm attempting to create a view so that I can utilize SSIS to export the file, however I'm stuck as I'm not finding a way to create a view from the below query because it uses a temp table. Any suggestions will be much appriciated.

Note that running the below by itself works fine and returns the proper result set that's needed. Just when I try to wrap it in the create view it's creating problems.

CREATE VIEW OFRESULTS

AS

CREATE TABLE #t1
(
ID INT IDENTITY(1, 1) Primary Key
,IncNum nvarchar (255)
,ParentIncNum nvarchar (255)
,InAbbr nvarchar (255)
,SpecificInc nvarchar (255)
,CCSection nvarchar (255)
,PCode nvarchar (255)
,IncTime nvarchar (255)
,IncFac nvarchar (255)
,Location nvarchar (255)
,IncDate nvarchar (255)
,Program nvarchar (255)
,Extra nvarchar (255)
,Fac nvarchar (255)
,IncVoid nvarchar (255)
)

INSERT Into #t1 SELECT a.IncNum, 
CASE WHEN RIGHT(a.IncNum, 2) like '[a-z][0-9]%' THEN LEFT(a.IncNum, 
LEN(a.IncNum) - 2) ELSE a.IncNum END As ParentID, a.IncAbbr,
COALESCE(a.IncSpc, l.IncSpc),
a.IncVio,
a.IncPC,
CONVERT(varchar(8), a.IncTime, 108) as IncTime,
a.IncSite,
a.IncLoc,
CONVERT(varchar(10), a.IncDate, 101) as IncDate,
a.IncProg,
CASE WHEN ex.IncExtCalc = 1 Then 'Controlled'
     WHEN ex.IncExtEme = 1 Then 'Immediate'
     WHEN ex.IncExtCalc = 1 and ex.IncExtEme = 1 Then 'Controlled and Immediate'
     ELSE ' '
END AS [Extr],
CASE WHEN a.IncFa = 1 Then 'Lvl I'
     WHEN     a.IncFac2 = 1 Then 'Lvl II'
     WHEN     a.IncFac3 = 1 Then 'Lvl III'
     WHEN     a.IncFac4 = 1 Then 'Lvl IV'
END AS [FacLevel],
l.IncVoid
  FROM [DBSQL].[dbo].[tblA] as a full outer join [DBSQL].[dbo].[tblLog] as l
  on a.IncNum = l.IncNum
  full outer join [DBSQL].[dbo].[tblEXTR] as ex on a.IncNum = ex.IncNum
 WHERE 
  a.IncDate >= DATEADD(day, -21, Getdate()) and a.IncDate <= DATEADD(day, -7, GetDate()) and l.IncVoid = 0
  or
  a.IncDate >= DATEADD(day, -21, Getdate()) and a.IncDate <= DATEADD(day, -7, GetDate()) and l.IncVoid is null



SELECT tt.*
FROM #t1 tt
INNER JOIN
    (
    SELECT ParentIRNum, MAX(ID) AS MaxID
    FROM #t1
    GROUP BY ParentIRNum
    ) groupedtt ON tt.ParentIRNum = groupedtt.ParentIRNum  AND tt.ID = groupedtt.MaxID
       ORDER BY ID 


DROP Table #t1

Answer 1

I see this question has already been answered but I figured I will add my 10 cents anyway. The query in question does not need temp tables and can be re-written using CTE’s which will make it compatible with a View as per example below:

CREATE VIEW MyView 
AS
WITH CTE_T1
AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 1 AS [Order])) as ID 
            ,a.IncNum 
            ,(
                CASE 
                    WHEN RIGHT(a.IncNum, 2) like '[a-z][0-9]%' THEN LEFT(a.IncNum, LEN(a.IncNum) - 2) 
                    ELSE a.IncNum 
                END
            )                                       AS ParentIncNum
            ,a.IncAbbr                              AS InAbbr
            ,COALESCE(a.IncSpc, l.IncSpc)           AS SpecificInc
            ,a.IncVio                               AS CCSection
            ,a.IncPC                                AS PCode
            ,CONVERT(varchar(8), a.IncTime, 108)    AS IncTime
            ,a.IncSite                              AS IncFac
            ,a.IncLoc                               AS [Location]
            ,CONVERT(varchar(10), a.IncDate, 101)   AS IncDate
            ,a.IncProg                              AS [Program]
            ,(
                CASE WHEN ex.IncExtCalc = 1 Then 'Controlled'
                    WHEN ex.IncExtEme = 1 Then 'Immediate'
                    WHEN ex.IncExtCalc = 1 and ex.IncExtEme = 1 Then 'Controlled and Immediate'
                    ELSE ' '
                END 
            )                                       AS [Extra]
            ,(
                CASE WHEN a.IncFa = 1 Then 'Lvl I'
                    WHEN     a.IncFac2 = 1 Then 'Lvl II'
                    WHEN     a.IncFac3 = 1 Then 'Lvl III'
                    WHEN     a.IncFac4 = 1 Then 'Lvl IV'
                END 
            )                                       AS [Fac]
            ,l.IncVoid                              AS IncVoid
    FROM [DBSQL].[dbo].[tblA] as a full outer join [DBSQL].[dbo].[tblLog] as l
        on a.IncNum = l.IncNum
    FULL OUTER JOIN [DBSQL].[dbo].[tblEXTR] as ex on a.IncNum = ex.IncNum
    WHERE 
      a.IncDate >= DATEADD(day, -21, Getdate()) and a.IncDate <= DATEADD(day, -7, GetDate()) and l.IncVoid = 0
      or
      a.IncDate >= DATEADD(day, -21, Getdate()) and a.IncDate <= DATEADD(day, -7, GetDate()) and l.IncVoid is null

), CTE_GroupedTT
AS
(
    SELECT  ParentIncNum
            ,MAX(ID) AS MaxID
    FROM CTE_T1
    GROUP BY ParentIncNum
)
SELECT      TOP 100 PERCENT
            tt.*
FROM        CTE_T1 tt
INNER JOIN  CTE_GroupedTT groupedtt ON tt.ParentIncNum = groupedtt.ParentIncNum  AND tt.ID = groupedtt.MaxID
ORDER BY    ID

In the above example CTE_T1 performs the same function as the temporary table #t1.

Answer 2

If you absolutely need to use a temp table, you can put it within a Stored procedure and then call/execute it. Something like the below might work.

 CREATE PROCEDURE dbo.UsingTempTables
 AS 
 BEGIN
 SET NOCOUNT ON;

CREATE TABLE #TempTable(ColumnA INT, ColumnB Varchar(50));

INSERT INTO #TempTable(ColumnA,ColumnB) VALUES(5,'ABC');
INSERT INTO #TempTable(ColumnA,ColumnB) VALUES(6,'DEF');

SELECT ColOne = ColumnA
    ,  ColTwo = ColumnB
    FROM #TempTable
 END;

Then use something like the below in the execute sql task in SSIS:

 EXEC('EXEC dbo.UsingTempTables') 
 WITH RESULT SETS
 ( ( ColOne INT, ColTwo VARCHAR(50)) )

FYI: Check out the below link for additional reference (the above answer is based on it) https://www.sqlservercentral.com/Forums/Topic1574990-364-1.aspx

Answer 3

I would think you could use a stored procedure or a table valued function with in-memory tables (declare @t1 table...).

Or you can run that same script without the SSIS and use bcp or openrowset to write data to a file.