blocks|key|521358|text|SELECT+...+INTO需要在从CTE中选择。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|521359|;WITH+Calendar
+++++AS+(SELECT+/*...+Rest+of+CTE+definition+removed+for+clarity*/)
SELECT+EventID,
+++++++EventStartDate,
+++++++EventEndDate,
+++++++PlannedDate+++++++++++++++++++AS+[EventDates],
+++++++Cast(PlannedDate+AS+DATETIME)+AS+DT,
+++++++Cast(EventStartTime+AS+TIME)++AS+ST,
+++++++Cast(EventEndTime+AS+TIME)++++AS+ET,
+++++++EventTitle,
+++++++EventType
INTO+TEMPBLOCKEDDATES+/*+<----+INTO+goes+here*/++++++++
FROM+++Calendar
WHERE++(+PlannedDate+>=+Getdate()+)
+++++++AND+','+%2B+EventEnumDays+%2B+','+LIKE+'%25,'+%2B+Cast(Datepart(dw,+PlannedDate)+AS+CHAR(1))+%2B+',%25'
++++++++OR+EventEnumDays+IS+NULL
ORDER++BY+EventID,
++++++++++PlannedDate
OPTION+(maxrecursion+0)+|code-block|syntax|javascript|521360|entityMap^0|0|F|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

The <code>SELECT ... INTO</code> needs to be in the select from the CTE.

<pre><code>;WITH Calendar
 AS (SELECT /*... Rest of CTE definition removed for clarity*/)
SELECT EventID,
 EventStartDate,
 EventEndDate,
 PlannedDate AS [EventDates],
 Cast(PlannedDate AS DATETIME) AS DT,
 Cast(EventStartTime AS TIME) AS ST,
 Cast(EventEndTime AS TIME) AS ET,
 EventTitle,
 EventType
INTO TEMPBLOCKEDDATES /* &lt;---- INTO goes here*/ 
FROM Calendar
WHERE ( PlannedDate &gt;= Getdate() )
 AND ',' + EventEnumDays + ',' LIKE '%,' + Cast(Datepart(dw, PlannedDate) AS CHAR(1)) + ',%'
 OR EventEnumDays IS NULL
ORDER BY EventID,
 PlannedDate
OPTION (maxrecursion 0) 
</code></pre>

blocks|key|521442|text|实际上，格式可以非常简单-有时不需要预定义临时表-它将从select的结果创建。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|521443|Select+FieldA...FieldN+
into+#MyTempTable+
from+MyTable|code-block|syntax|javascript|521444|因此，除非你想要不同的类型，或者对定义非常严格，否则请保持简单。另请注意，在存储过程中创建的任何临时表都会在存储过程执行完毕时自动删除。如果存储过程A创建了一个临时表并调用了存储过程B，那么B将能够使用A创建的临时表。|521445|然而，显式地删除您创建的每个临时表通常被认为是很好的编码实践。|521446|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Really the format can be quite simple - sometimes there's no need to predefine a temp table - it will be created from results of the select.

<pre><code>Select FieldA...FieldN 
into #MyTempTable 
from MyTable
</code></pre>

So unless you want different types or are very strict on definition, keep things simple. Note also that any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temp table and calls stored procedure B, then B will be able to use the temporary table that A created. 

However, it's generally considered good coding practice to explicitly drop every temporary table you create anyway.

blocks|key|521512|text|如何在存储过程中使用TempTable？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|521513|以下是步骤：|521514|创建临时表|521515|--+CREATE+TEMP+TABLE+
Create+Table+#MyTempTable+(
++++EmployeeID+int
);|code-block|syntax|javascript|521516|在临时表中插入临时选择数据|521517|--+INSERT+COMMON+DATA
Insert+Into+#MyTempTable
Select+EmployeeID+from+[EmployeeMaster]+Where+EmployeeID+between+1+and+100|521518|SELECT+TEMP表(现在可以使用此select查询)|521519|Select+EmployeeID+from+#MyTempTable|521520|最后一步删除表|521521|Drop+Table+#MyTempTable|521522|我希望这能有所帮助。简单明了:)|521523|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

How to Use TempTable in Stored Procedure?

Here are the steps:

CREATE TEMP TABLE

<pre><code>-- CREATE TEMP TABLE 
Create Table #MyTempTable (
 EmployeeID int
);
</code></pre>

INSERT TEMP SELECT DATA INTO TEMP TABLE

<pre><code>-- INSERT COMMON DATA
Insert Into #MyTempTable
Select EmployeeID from [EmployeeMaster] Where EmployeeID between 1 and 100
</code></pre>

SELECT TEMP TABLE (You can now use this select query)

<pre><code>Select EmployeeID from #MyTempTable
</code></pre>

FINAL STEP DROP THE TABLE

<pre><code>Drop Table #MyTempTable
</code></pre>

I hope this will help. Simple and Clear :)

blocks|key|521572|text|Select++++++Eventname,+
++++++++++++count(Eventname)+as+'Counts'
INTO++++++++#TEMPTABLE++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
FROM++++++++tblevent
where+++++++Eventname+like+'A%25'
Group+by++++Eventname
order+by++++count(Eventname)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|521573|在这里，通过使用into子句可以直接创建表|unstyled|521574|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>Select Eventname, 
 count(Eventname) as 'Counts'
INTO #TEMPTABLE 
FROM tblevent
where Eventname like 'A%'
Group by Eventname
order by count(Eventname)
</code></pre>

Here by using the into clause the table is directly created

blocks|key|304906|text|以下是对在执行时创建表的查询的答案的一个细微更改(即，您不必首先创建表)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|304907|SELECT+*+INTO+#Temp
FROM+(
select+OptionNo,+OptionName+from+Options+where+OptionActive+=+1
)+as+X|code-block|syntax|javascript|304908|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here's one slight alteration to the answers of a query that creates the table upon execution (i.e. you don't have to create the table first):

<pre><code>SELECT * INTO #Temp
FROM (
select OptionNo, OptionName from Options where OptionActive = 1
) as X
</code></pre>

blocks|key|304937|text|您还可以使用视图创建临时/虚拟表create+View+view_name+AS+SELECT+column1，column2，...来自table_name的WHERE条件；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|304938|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can also use View to create a temporary/virtual table
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

I have a MS SQL CTE query from which I want to create a temporary table. I am not sure how to do it as it gives an <code>Invalid Object name</code> error.

Below is the whole query for reference

<pre><code>SELECT * INTO TEMPBLOCKEDDATES FROM 
;with Calendar as (
 select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, EventStartDate as PlannedDate
 ,EventType from EventCalender
 where EventActive = 1 AND LanguageID =1 AND EventBlockDate = 1
 union all
 select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, dateadd(dd, 1, PlannedDate)
 ,EventType from Calendar
 where EventRecurring = 1
 and dateadd(dd, 1, PlannedDate) &lt;= EventEndDate 
)
select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle
,EventType from Calendar
where (PlannedDate &gt;= GETDATE()) AND ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%'
 or EventEnumDays is null
order by EventID, PlannedDate
option (maxrecursion 0)
</code></pre>

I would appreciate a point in the right direction or if I can create a temporary table from this CTE query

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

我有一个MS SQL CTE查询，我想从它创建一个临时表。我不确定该怎么做，因为它会给出一个Invalid Object name错误。以下是整个查询以供参考SELECT * INTO TEMPBLOCKEDDATES FROM ;with Calendar as (    select EventID, EventT...

问如何使用SELECT * INTO tempTable FROM CTE查询创建临时表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SELECT * INTO tempTable FROM CTE查询创建临时表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SELECT * INTO tempTable FROM CTE查询创建临时表
EN