blocks|key|1194034|text|是的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1194035|WITH+tab+(
++bla+bla
)

INSERT+INTO+dbo.prf_BatchItemAdditionalAPartyNos+(++BatchID,++++++++++++++++++++++++++++++++++++++++++++++++++++++++AccountNo,
APartyNo,
SourceRowID)++++

SELECT+*+FROM+tab|code-block|syntax|javascript|1194036|注意，这是针对SQL+Server的，它支持多个CTE：|1194037|WITH+x+AS+(),+y+AS+()+INSERT+INTO+z+(a,+b,+c)+SELECT+a,+b,+c+FROM+y|1194038|Teradata只允许一个CTE，语法如您的示例所示。|1194039|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Yep:

<pre><code>WITH tab (
 bla bla
)

INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos ( BatchID, AccountNo,
APartyNo,
SourceRowID) 

SELECT * FROM tab
</code></pre>

Note that this is for SQL Server, which supports multiple CTEs:

<pre><code>WITH x AS (), y AS () INSERT INTO z (a, b, c) SELECT a, b, c FROM y
</code></pre>

Teradata allows only one CTE and the syntax is as your example.

blocks|key|1194082|text|您需要首先放置CTE，然后将INSERT+INTO与select语句组合在一起。此外，CTE名称后面的"AS“关键字也不是可选的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1194083|WITH+tab+AS+(
++++bla+bla
)
INSERT+INTO+dbo.prf_BatchItemAdditionalAPartyNos+(
BatchID,
AccountNo,
APartyNo,
SourceRowID
)++
SELECT+*+FROM+tab|code-block|syntax|javascript|1194084|请注意，代码假设CTE将恰好返回四个字段，并且这些字段在顺序和类型上与INSERT语句中指定的字段相匹配。如果不是这样，只需将"SELECT+*“替换为所需字段的特定选择即可。|1194085|至于你关于使用函数的问题，我会说“视情况而定”。如果您只是因为性能原因而将数据放在表中，并且通过函数使用它的速度是可以接受的，那么我会考虑使用函数。另一方面，如果您需要在几个不同的查询中使用CTE的结果，并且速度已经是一个问题，我会使用表(常规的或临时的)。|1194086|WITH+common_table_expression+(Transact-SQL)|offset|length|1194087|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms175972%2528v=sql.105%2529.aspx^0|0|0|0|0|0|17|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

You need to put the CTE first and then combine the INSERT INTO with your select statement. Also, the "AS" keyword following the CTE's name is not optional:

<pre><code>WITH tab AS (
 bla bla
)
INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (
BatchID,
AccountNo,
APartyNo,
SourceRowID
) 
SELECT * FROM tab
</code></pre>

Please note that the code assumes that the CTE will return exactly four fields and that those fields are matching in order and type with those specified in the INSERT statement.
If that is not the case, just replace the "SELECT *" with a specific select of the fields that you require.

As for your question on using a function, I would say "it depends". If you are putting the data in a table just because of performance reasons, and the speed is acceptable when using it through a function, then I'd consider function to be an option.
On the other hand, if you need to use the result of the CTE in several different queries, and speed is already an issue, I'd go for a table (either regular, or temp).

<a href="http://msdn.microsoft.com/en-us/library/ms175972%28v=sql.105%29.aspx" rel="noreferrer">WITH common_table_expression (Transact-SQL)</a>

blocks|key|979752|text|在这里，我来晚了，但出于我的目的，我希望能够运行用户输入的代码并将其存储在临时表中。使用oracle没有这样的问题..insert位于语句的开头，在with子句之前。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|979753|要在sql+server中使用此功能，请执行以下操作：|979754|插入到#stagetable+execute+(@InputSql)中|979755|(因此，select语句@inputsql可以作为with子句开始)。|979756|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

Late to the party here, but for my purposes I wanted to be able to run the code the user inputted and store in a temp table. Using oracle no such issues.. the insert is at the start of the statement before the with clause.
For this to work in sql server, the following worked:
INSERT into #stagetable execute (@InputSql)
(so the select statement @inputsql can start as a with clause).

I have a very complex CTE and I would like to insert the result into a physical table. 

Is the following valid?

<pre><code>INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos 
(
 BatchID,
 AccountNo,
 APartyNo,
 SourceRowID
) 
WITH tab (
 -- some query
) 
SELECT * FROM tab
</code></pre>

I am thinking of using a function to create this CTE which will allow me to reuse. Any thoughts?

Combining INSERT INTO and WITH/CTE

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个非常复杂的CTE，我想将结果插入到物理表中。以下内容是否有效？INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (    BatchID,    AccountNo,    APartyNo,    SourceRowID)       WITH tab (  -...

问组合INSERT INTO和WITH/CTE
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问组合INSERT INTO和WITH/CTEEN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问组合INSERT INTO和WITH/CTE
EN