组合 INSERT INTO 和 WITH/CTE

我有一个非常复杂的 CTE，我想把结果插入到一个物理表。

以下内容有效吗？

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

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

261955 次浏览
小开

是的:

WITH tab (
bla bla
)


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


SELECT * FROM tab

注意，这是针对 SQLServer 的，它支持多个 CTE:

WITH x AS (), y AS () INSERT INTO z (a, b, c) SELECT a, b, c FROM y

Teradata 只允许一个 CTE，语法就是您的示例。

小开
最佳答案

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

WITH tab AS (
bla bla
)
INSERT INTO dbo.prf_BatchItemAdditionalAPartyNos (
BatchID,
AccountNo,
APartyNo,
SourceRowID
)
SELECT * FROM tab
请注意，代码假设 CTE 将返回正好4个字段，并且这些字段按顺序和类型与 INSERT 语句中指定的字段匹配。 如果不是这样的话，只需用您需要的特定字段替换“ SELECT *”即可。译注: 至于你关于使用函数的问题，我想说“这取决于”。如果仅仅是因为性能原因而将数据放入表中，并且在通过函数使用数据时速度是可以接受的，那么我会考虑使用函数。 另一方面，如果您需要在几个不同的查询中使用 CTE 的结果，并且速度已经是一个问题，那么我将使用表(常规或临时表)。译注:

Rel = “ noReferrer”> WITHcommon _ table _ expression (Transact-SQL)

小开

公共表表达式的 WITH子句位于顶部。

在 CTE 中包装每个插入的好处是可视地将查询逻辑与列映射隔离开来。

发现错误:

WITH _INSERT_ AS (
SELECT
[BatchID]      = blah
,[APartyNo]     = blahblah
,[SourceRowID]  = blahblahblah
FROM Table1 AS t1
)
INSERT Table2
([BatchID], [SourceRowID], [APartyNo])
SELECT [BatchID], [APartyNo], [SourceRowID]
FROM _INSERT_

同样的错误:

INSERT Table2 (
[BatchID]
,[SourceRowID]
,[APartyNo]
)
SELECT
[BatchID]      = blah
,[APartyNo]     = blahblah
,[SourceRowID]  = blahblahblah
FROM Table1 AS t1

只需几行样板代码就可以非常容易地验证代码是否按照正确的顺序插入了正确的列数，即使有非常多的列。未来的你会感谢你的。

小开

这里的聚会已经迟到了，但是出于我的目的，我希望能够运行用户输入的代码并将其存储在临时表中。使用 Oracle 没有这样的问题。.插入位于 with 子句之前的语句的开头。

要在 sql 服务器中实现这一点，以下步骤可以实现:

将 INSERT 插入到 # stagetable execute (@InputSql)中

(因此 select 语句@inputsql 可以从 with 子句开始)。


提交答案