将INSERT INTO和WITH / CTE结合 [英] Combining INSERT INTO and WITH/CTE

问题描述

我的CTE非常复杂，我想将结果插入物理表中。

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

以下有效吗？

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

我正在考虑使用函数创建此CTE这将允许我重复使用。有什么想法吗？

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

推荐答案

您需要先放置CTE，然后将INSERT INTO与select语句组合在一起。此外，CTE名称后面的 AS关键字不是可选的：

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:

WITH tab AS (
    bla bla
)
INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (
BatchID,
AccountNo,
APartyNo,
SourceRowID
)  
SELECT * FROM tab

请注意，代码假定CTE将恰好返回四个字段，并且这些字段在使用INSERT语句中指定的顺序和类型。
如果不是这种情况，只需将 SELECT *替换为所需的特定字段即可。

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.

关于使用问题一个功能，我会说取决于。如果您只是出于性能原因将数据放在表中，并且通过函数使用数据时速度是可以接受的，那么我认为函数是一种选择。
另一方面，如果您需要在几个不同的查询中使用CTE的结果，而速度已经成为问题，我就去找一张表（常规表或临时表）。

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).

使用common_table_expression（Transact-SQL）

这篇关于将INSERT INTO和WITH / CTE结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！