sql - INSERT INTO WITH 公用表表达式 - SQL Server

Question

这个问题在这里已经有了答案:





Combining INSERT INTO and WITH/CTE

(4 个回答)


6年前关闭。




我已经想出如何使用通用表表达式递归查找所有向某个经理汇报的员工(感谢 StackOverflow!)。

这是对我有用的代码:

WITH MyCTE AS 
(
    SELECT [WWID] FROM [x500]..[WorkerPublicExtended] 
    WHERE [MgrWWID] = '10624529' AND ([StatCode] = 'A') AND ([BadgeType] = 'BB') 
    UNION ALL 
    SELECT [WorkerPublicExtended].[WWID] FROM [x500]..[WorkerPublicExtended] 
        INNER JOIN MyCTE ON [WorkerPublicExtended].[MgrWWID] = MyCTE.WWID 
    WHERE [WorkerPublicExtended].[MgrWWID] IS NOT NULL 
        AND ([BadgeType] = 'BB') AND ([StatCode] = 'A')
) 

SELECT *, 'MGR+10624529' AS [source] FROM MyCTE

这完美地工作。但是，如果我尝试将其插入到另一个表中(这是最终目标)，我找不到任何不会引发一个或多个错误的代码的语法变体。有人可以帮我把这些放在一起吗？

INSERT INTO [LTDtraining].[dbo].[pop00001]
WITH MyCTE AS 
(
    SELECT [WWID] FROM [x500]..[WorkerPublicExtended] 
    WHERE [MgrWWID] = '10624529' AND ([StatCode] = 'A') AND ([BadgeType] = 'BB') 
    UNION ALL 
    SELECT [WorkerPublicExtended].[WWID] FROM [x500]..[WorkerPublicExtended] 
        INNER JOIN MyCTE ON [WorkerPublicExtended].[MgrWWID] = MyCTE.WWID 
    WHERE [WorkerPublicExtended].[MgrWWID] IS NOT NULL 
        AND ([BadgeType] = 'BB') AND ([StatCode] = 'A')
) 

SELECT *, 'MGR+10624529' AS [source] FROM MyCTE

它抛出如下错误:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MyCTE'.

或者永远流行的

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.

Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.

如果我插入建议的分号，它会响应

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ';'.

所以这让我觉得我不知道我在这里做什么，SQL 也不知道。

Answer 1

通过移动 INSERT 可以轻松修复该错误。 WITH 下方的声明像这样:

WITH MyCTE AS 
(
    SELECT [WWID] FROM [x500]..[WorkerPublicExtended] 
    WHERE [MgrWWID] = '10624529' AND ([StatCode] = 'A') AND ([BadgeType] = 'BB') 
    UNION ALL 
    SELECT [WorkerPublicExtended].[WWID] FROM [x500]..[WorkerPublicExtended] 
        INNER JOIN MyCTE ON [WorkerPublicExtended].[MgrWWID] = MyCTE.WWID 
    WHERE [WorkerPublicExtended].[MgrWWID] IS NOT NULL 
        AND ([BadgeType] = 'BB') AND ([StatCode] = 'A')
)

INSERT INTO [LTDtraining].[dbo].[pop00001] 
SELECT *, 'MGR+10624529' AS [source] FROM MyCTE