结合 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屋！