Select into statement in a view

Question

I want make a view of a select statement that uses a temp table but I am getting errors about views may not have temp tables. How can I resolve this? This is my query

CREATE VIEW vwTopStackedItems
AS
SELECT COUNT(INVENTORY.itemID) as Stacks, (ITEM.itemID), CHARACTERS.charName      
INTO #Table1
FROM INVENTORY
INNER JOIN ITEM
ON INVENTORY.itemID = ITEM.itemID
JOIN CHARACTERS
ON INVENTORY.charID = CHARACTERS.charID
WHERE INVENTORY.quantity>1
GROUP BY CHARACTERS.charName, ITEM.itemID 


SELECT [ITEMID], 
   LEFT(column_names , LEN(column_names )-1) AS column_names, 
   SUM([Stacks]) total_stacks
FROM #Table1 AS extern
CROSS APPLY
(
SELECT [charName] + ','
   FROM #Table1 intern
    WHERE intern.[ITEMID] = extern.[ITEMID]
FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY [ITEMID], column_names;
GO

You just plain can't. But I think you can use a CTE in a view. — paparazzo, Jun 22 '16 at 17:05

score 2 · Accepted Answer · answered Jun 22 '16 at 17:01

You cannot use temp tables when creating views. You could try using a common-table-expression instead. Not 100% sure using it with cross apply will work though. If not, another option would be to move the temp table to inline subqueries instead.

Here's with a CTE:

CREATE VIEW vwTopStackedItems
AS
WITH CTE AS (
    SELECT COUNT(INVENTORY.itemID) as Stacks, (ITEM.itemID), CHARACTERS.charName      
    FROM INVENTORY
    INNER JOIN ITEM
    ON INVENTORY.itemID = ITEM.itemID
    JOIN CHARACTERS
    ON INVENTORY.charID = CHARACTERS.charID
    WHERE INVENTORY.quantity>1
    GROUP BY CHARACTERS.charName, ITEM.itemID 
)
SELECT [ITEMID], 
   LEFT(column_names , LEN(column_names )-1) AS column_names, 
   SUM([Stacks]) total_stacks
FROM CTE AS extern
CROSS APPLY
(
   SELECT [charName] + ','
   FROM CTE intern
   WHERE intern.[ITEMID] = extern.[ITEMID]
   FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY [ITEMID], column_names;
GO

However, looking over your code... this is much more elegant. Nice! — RThomas, Jun 22 '16 at 17:11
It works thank you so much. Just learned what a CTE is thanks — Tristan, Jun 22 '16 at 17:22

score 1 · Answer 2 · edited Jun 22 '16 at 16:58

1

A view consists of a single SELECT statement. You cannot create or drop tables in a view.

If you need to do something like that try using stored procedure.

edited Jun 22 '16 at 16:58

Arulkumar

12,966
14
47
68

answered Jun 22 '16 at 16:56

Whencesoever

2,218
15
26

This is the most succinct and obvious answer. A view is a "virtual table". Clearly you should not be doing anything but selecting what to show in that virtual table. If you need to make modifications to the source data in any way, a view is not the correct tool. – pzkpfw Nov 28 '17 at 09:35

Matt · Answer 3 · 2016-06-22T17:08:09.737

Views and Functions may not modify data or schema within the database. So to use a temp table in this capacity you need to use a stored procedure.

You could use a table variable in a table valued function but that will be a decent amount of overhead that may be unnecessary.

You can switch your temp table statement to a CTE and even though it won't create a temp table it will organize your code the way you want.

Select into statement in a view

3 Answers3